Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Sort by Color in Excel
  • How to Create Area Chart in Excel
  • How To Filter Data in Excel
  • Excel Pie Chart
  • Everything about Charts in Excel

References

  • How to use Excel TRANSPOSE Function
  • How to get address of last cell in range in Excel
  • How to use Excel OFFSET function
  • Convert text string to valid reference in Excel using Indirect function
  • Basic INDEX MATCH approximate in Excel

Data Validations

  • Excel Data validation date in next 30 days
  • Excel Data validation unique values only
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain
  • Excel Data validation with conditional list

Bond valuation example in Excel

by

This tutorial show how to calculate the value of a bond on the issue date using the PV function.

Explanation

In the example shown, the formula in C10 is:

=-PV(C6/C8,C7*C8,C5/C8*C4,C4)

Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the value of a bond on any date.

Worked Example:   PMT, RATE, NPER, PV and FV Financial Functions in Excel

How this formula works

In the example shown, we have a 3-year bond with a face value of $1,000. The coupon rate is 7% so the bond will pay 7% of the $1,000 face value in interest every year, or $70. However, because interest is paid semiannually in two equal payments, there will be 6 coupon payments of $35 each. The $1,000 will be returned at maturity. Finally, the required rate of return (discount rate) is assumed to be 8%.

Worked Example:   How to calculate present value of annuity in excel

The value of an asset is the present value of its cash flows. In this example we use the PV function to calculate the present value of the 6 equal payments plus the $1000 repayment that occurs when the bond reaches maturity. The PV function is configured as follows:

=-PV(C6/C8,C7*C8,C5/C8*C4,C4)

The arguments provided to PV are as follows:

Worked Example:   PV function: Description, Usage, Syntax, Examples and Explanation

rate – C6/C8 = 8%/2 = 4%

nper – C7*C8 = 3*2 = 6

pmt – C5/C8*C4 = 7%/2*1000 = 35

fv – 1000

The PV function returns -973.79. To get positive dollars, we use a negative sign before the PV function to get final result of $973.79

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • How to use IFS function in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Add workdays no weekends in Excel
  • Convert Excel time to decimal hours in Excel
  • Calculate number of hours between two times in Excel
  • How to calculate future date say 6 months ahead in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel

General

  • How to generate random number between two numbers in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • Index and match on multiple columns in Excel
  • How to increase by percentage in Excel
  • Create dynamic workbook reference to another workbook in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning