Explanation
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%.
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:
rate - C6/C8 = 8%/2 = 4%
nper - C7C8 = 32 = 6
pmt - C5/C8C4 = 7%/21000 = 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
Between coupon payment dates
In the example above, it is relatively straightforward to find the value of a bond on a coupon payment date with the PV function. Finding the value of a bond between coupon payment dates is more complex because interest does not compound between payments. The PRICE function can be used to calculate the “clean price” of a bond on any date.
More detail
For a more detailed explanation of bond valuation, see this article on tvmcalcs.com .
Explanation
CAGR stands for Compound Annual Growth Rate. CAGR is the average rate of return for an investment over a period of time. It is the rate of return required for an investment to grow from the starting balance to the ending balance, assuming profits are reinvested each year, and interest compounds annually. There are several ways to calculate CAGR in Excel.
CAGR with the RRI function
In Excel 2013 and later, you can use the RRI function to calculate CAGR with a simple formula. The formula in H9 is:
=RRI(B11,C6,C11)
where C11 is the ending value in year 5, C6 is the starting value (initial investment), and B11 is the total number of periods.
Note: unlike most other financial functions in Excel, fv (future value, the third argument) does not need to be entered as a negative number in RRI.
CAGR with a manual formula
The formula for calculating CAGR manually is:
=(end/start)^(1/periods)-1
In the example shown, the formula in H7 is:
=(C11/C6)^(1/B11)-1
where C11 is the ending value in year 5, C6 is the starting value or initial investment, and B11 is the total number of periods.
The first part of the formula is a measure of total return, and the second part of the formula annualizes the return over the life of the investment.
CAGR with the GEOMEAN function
The GEOMEAN function calculates geometric mean, and can also be used to calculate CAGR. To calculate CAGR with GEOMEAN, we need to use relative changes (percentage change + 1), sometimes called a growth factor. We have these values already in column E so we can use them directly in GEOMEAN the function. The formula in H8 is:
=GEOMEAN(E7:E11)-1
