Excel Formulas To Calculate The Bond Valuation

Assume that you’ve been assigned a task of calculating bond valuation; so if you are new to Ms Excel or do not have much experience with it, then I am pretty sure about it that doing this task manually might be your first approach, which is fine, but what will you do if you were assigned to compute bond valuation again and again with various values? If you still prefer to do it manually, then let me add that it will waste a lot of your time, and there is a probability that you will not be able to finish your task on time.

calculate bond valuation1

But don’t worry since you’ve just landed on the right article where you’ll learn an efficient approach to calculate bond valuation in a matter of seconds.

So, without further ado, let’s dive into it.

General Formula:

In Excel, use the formulae to compute the value of a bond on the issuance date.

=-PV(B3/B6,B5*B6,B4/B6*B2,B2)

Explanations of Syntax:

In order to use this formula to complete your task, you must be knowledgeable of the syntax used in that formula.

  • PV: The PV function in Excel is a financial tool that calculates the present value of an investment.
  • Division (/): This symbol divides values or integers.
  • The plus operator (+): This operator is used to add the values.
  • Minus Operator (-): Use this symbol to subtract any two numbers.
  • Multiplication (*): In this symbol, any two values or numbers will be multiplied.
  • The comma symbol (,): This symbol is a separator that aids in the separation of a list of values.
  • Parenthesis (): This symbol’s primary function is to group the elements.

Summary

The PV function may determine the value of a bond on the issuance date. In this post, we will look at two examples to better grasp the formula in F2. In the first case, the formula in F2 is as follows:

=-PV(B3/B6,B5*B6,B4/B6*B2,B2)

calculate bond valuation1

This example assumes that the issue date is today and that the next payment will be made in precisely 8 months. See the note below for information on calculating the value of a bond on any date.

Explanation

In the first example, we have a four-year bond with a $5,000 face value. Because the coupon rate is 8%, the bond will pay 8% of its face value in interest each year, or $400. However, because interest is paid in two equal installments semiannually, there will be eight $200 coupon payments. At maturity, the $5,000 will be repaid. Finally, the needed rate of return (discount rate) is set at 10%.

The present value of an asset’s cash flows is its worth. In this example, the PV function calculates the present value of the eight equal installments plus the $5000 payback when the bond matures. The PV function is set up as follows:

=-PV(B3/B6,B5*B6,B4/B6*B2,B2)

PV was given the following arguments:

  • rate – B3/B6 =10%/2=5%
  • nper – B5*B6=4*2=8
  • pmt – B4/B6*B2=8%/2*5000=200
  • fv – 5000

The PV function yields - 4676.84; thus, we apply a negative sign before the PV function to achieve the final result of $ 4676.84.

If you’re still confused, don’t worry because this second example will clear everything out for you.

In Example 2nd, we will see how to calculate bond valuation in Excel using the procedures below.

  • This section will enter the input values in Columns A and B.
  • Next, we will compute the value of a bond on the issuance date.

calculate bond valuation1

Ranges of Input

  • In any cell, type the formula mentioned above.

calculate bond valuation1

Enter the formula here.

  • Finally, you will obtain the result displayed below when you press the ENTER key.

calculate bond valuation1

Between the payout dates of coupons

In the above example, using the PV function to calculate the value of a bond on a coupon payment date is pretty simple. Because interest does not compound between payments, determining the value of a bond between coupon payment dates is more complicated. The PRICE function may compute a bond’s “clean price” on any date.

Conclusion:

In this lesson, we explained the formulae for calculating the valuation of a bond on the issuance date in Excel using two practical examples.

Related Functions

  • Excel PV function
    The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest rate.The syntax of the PV function is as below:= PV(rate,nper,pmt,[fv],[type])

Excel PV Function

This post will guide you how to use Excel PV function with syntax and examples in Microsoft excel.

Description

The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest rate. So you can use the PV function to get the present value based on a series of future payments.

The PV function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.

The PV function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the PV function is as below:

= PV(rate,nper,pmt,[fv],[type])

Where the PV function arguments are:

  • Rate -This is a required argument. The interest rate per period.
  • nPer -This is a required argument. The total number of payments periods in an annuity.
  • Pmt – This is a required argument. The amount of the payment made each period.
  • Fv – This is an optional argument. The future value or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
  • Type – This is an optional argument. The number 0 (zero) or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

 Note:

  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Excel PV Function Examples

The below examples will show you how to use Excel PV Function to calculate the present value of an investment.

#1 to get the present value of an annuity with the terms in A2:A4, using the following formula:

=PV(B1,B2,B3,B4)

excel pv examples1


Related Functions

  • Excel FV Function
    The Excel FV function used to calculate the future value of an investment based on a constant interest rate. The syntax of the FV function is as below:=FV(rate,nper,pmt,[pv],[type])…
  • Excel IPMT Function
    The Excel IPMT function used to calculate the interest payment for an investment based on a constant payment schedule and a constant interest rage.The syntax of the IPMT function is as below:= IPMT (rate, per, nper, pv, [fv], [type])…
  • Excel IRR Function
    The Excel IRR function returns the internal rate of return for a series of cash flows and the cash flows must be occurred at regular intervals (monthly or annually).The syntax of the IRR function is as below:=IRR(values, [guess])…
  • Excel ISPMT Function
    The Excel ISPMT function used to calculate the interest paid during a specific period of an investment.The syntax of the ISPMT function is as below:= ISPMT (rate, per, nper, pv)…
  • Excel MIRR Function
    The Excel MIRR function returns the modified internal rate of return for a series of cash flows and the cash flows must be occurred at regular intervals (monthly or annually). The syntax of the MIRR function is as below:=MIRR(values, finance_rate,reinvest_rate)…
    Excel NPER Function
    The Excel NPER function returns the number of periods for an investment or loan based on periodic payment amount and a constant interest rate.The syntax of the NPER function is as below:= NPER (rate, pmt, pv, [fv], [type])…
  • Excel NPV Function
    The Excel NPV function returns the net present value of an investment by using a discount rate and a series of future cash flows (future payments and income).The syntax of the NPV function is as below:= NPV (rate, Value1,[value2],…)…
  • Excel PMT Function
    The Excel PMT function returns the payment amount for a loan or investment based on constant payments and a constant interest rate.The syntax of the PMT function is as below:= PMT(rate, nper, pv,[fv],[type])…
  • Excel PPMT Function
    The Excel PPMT function returns the payment amount on the principal for a given period for a loan or investment based on constant payments and a constant interest rate. The syntax of the PPMT function is as below:=PPMT(rate, per,nper, pv,[fv],[type])…
  • Excel RATE Function
    The Excel RATE function returns the interest rate per payment period of an annuity.The syntax of the RATE function is as below:=RATE(nper, pmt,pv,[fv],[type],[guess])…