| EXCEL EXAMPLES |
| Example #1: | NET PRESENT VALUE |
| Discount Rate: | 12% |
| Year 0 | -65 |
| Year 1 | 10 |
| Year 2 | 20 |
| Year 3 | 40 |
| Year 4 | 65 |
| Year 5 | -20 |
| ANSWER: NPV (cash flows occur at the BEGINNING of each period) | $18.30 |
| In the NPV example, you don't include the year 0 cash flow of $65 (cell B6) inside the parenthesis because the payments occur at the BEGINNING of the first period. So the answer is $18.30. |
| Example #2 | INTERNAL RATE OF RETURN (IRR) |
| Year 0 | -65 |
| Year 1 | 10 |
| Year 2 | 20 |
| Year 3 | 40 |
| Year 4 | 65 |
| Year 5 | -20 |
| ANSWER | 22.41% |
| Example #3 | PRESENT VALUE |
| Year 0 | | (Discount $100 back 5 years at a 12% discount rate) |
| Year 1 | 12% | Discount Rate: |
| Year 2 | 5 | # Periods (or years) being discounted: |
| Year 3 | 100 | FV |
| Year 4 |
| Year 5 |
| ANSWER | $56.74 |
| Example #4 | FUTURE VALUE |
| Year 0 | | (Compound $100 up 5 years at a 12% discount rate) |
| Year 1 | 12% | Discount Rate: |
| Year 2 | 5 | # Periods (or years) being compounded: |
| Year 3 | 100 | PV |
| Year 4 |
| Year 5 |
| ANSWER | $176.23 |
| Example #5 | Finding N (Nper) Number of Periods (or Years) |
| Present Value = | $50 | How long would it take to compound $50 up to $100 using a 12% discount rate? |
| Future Value = | $100 |
| Discount Rate = | 12% |
| ANSWER | 6.12 | (Or 6.12 years. Note that you have to make either the FV or PV input negative for the formula to work) |
| | | How long would it take to discount $100 down to $25 using a 12% discount rate? |
| Present Value = | $100 |
| Future Value = | $25 |
| Discount Rate = | 12% |
| ANSWER | -12.23 | (Or 12.23 years. Note that years can not be negative. You have to make either the FV or PV input negative for the formula to work) |
| Example #6 | Finding I (Interest Rate) | If you start with $100 and end with $200 after 5 years, what was the annual interest rate earned? |
| Present Value = | $100 |
| Future Value = | $200 |
| N (Nper) | 5 |
| ANSWER | 14.87% | (or 14.87%. You must keep either the Present Value number or Future Value number negative.) |
| Payment (PMT) | $100 | If you receive payments of $100 each year for 5 years and end up with $750 after 5 years, what was the annual interest rate earned? |
| Future Value = | $750 |
| N (Nper) | 5 |
| ANSWER | 20.40% | (or 20.40%. Note that the Payment input or Future Value input must be negative for the formula to work) |
| Example #7 | Finding the Payment amount (PMT) or Annuity amount |
| Present Value = | $0 | What would have to be the annual payment amount (or annuity amount) to have $100,000 after 20 years with a 12% discount rate? |
| Future Value = | $100,000 |
| N (Nper) | 20 |
| Interest Rate | 12% |
| ANSWER | $1,387.88 | (Note: you want the FV input to be negative so your answer comes out positive.) |
| Example #8 | SUM, AVERAGE, VARIANCE, STANDARD DEVIATION & CORRELATION |
| | 0.12 | 0.09 |
| | 0.15 | 0.11 |
| | 0.08 | 0.15 |
| | 0.06 | 0.03 |
| | 0.08 | -0.12 |
| SUM | 0.4900 |
| AVERAGE | 0.0980 |
| VARIANCE | 0.0013 |
| STANDARD DEVIATION | 0.0363 |
| CORRELATION | 0.3928 |
| Example #9 | Calculating a Bonds Price |
| Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent, and a yield to |
| maturity of 9 percent. If the bond makes semiannual payments, what is its price today? |
| Settlement | 1/1/00 | (Think of Settlement as the beginning of the duration of the bond) |
| Maturity | 1/1/22 | (Think of Maturity as the end of the duration of the bond) |
| Rate | 0.08 | (Coupon Rate) |
| YTM | 0.09 | (Yield to Maturity or Required Rate fo Return) |
| Redemption | 100 | (Bonds Face Value, Par Value, or Fair Price; Note that is is $100, not $1,000. You make the adjustments by multiplying the answer by 10.) |
| Frequency | 2 | (Coupon payments are semiannul, so you put in a 2) |
| Basis | 0 | (Always leave it blank) |
| Bond price (% of par): | 90.49 | (The answer. But you need to multiply it by 10 to get the actual bond price.) |
| Multiply by 10 | 904.91 | (Microsoft gives the bond price in 2 digits like in cell B14. You need to multiply it by 10 to get the actual bond price) |
| | | (ANSWER = 904.91) |
| Example #10 | Calculating a Bonds Yield to Maturity |
| Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent and a price of |
| $960.17. If the bond make semiannual payments, what is its yield to maturity? |
| Settlement | 1/1/00 | (Think of Settlement as the beginning of the duration of the bond) |
| Maturity | 1/1/22 | (Think of Maturity as the end of the duration of the bond) |
| Rate | 0.08 | (Coupon Rate) |
| Pr | 96.017 | (The bonds price per $100 face value) |
| Redemption | 100 | (Bonds Face Value, Par Value, or Fair Price; Note that is is $100, not $1,000. You make the adjustments by multiplying the answer by 10.) |
| Frequency | 2 | (Coupon payments are semiannul, so you put in a 2) |
| Basis: | 0 | (Always leave it blank) |
| Yield to Maturity: | 8.40% | (ANSWER = 8.40%) |
| Example #11 | Calculating the Effective Annual Interest Rate |
| Supose you have a Nominal Interest Rate of 5.25% that is compounded quarterly (4 times) during the year. What is the Effective Annual Interest Rate? |
| Nominal Interest Rate: | 5.25% |
| Npery (Number of compounding periods per year) | 4 |
| Effective Annual Interest Rate: | 5.3543% | (ANSWER = 5.35%) |
| (Note: The EAR is always higher than the Nominal Rate as long as there is more than 1 compounding period per year. If you increase the compounding periods per year, the Effective Annual Rate will increase, but at a decreasing rate). |
| Example #12 | Calculating the Annual Nominal Interest Rate |
| Supose you have an Effective Annual Interest Rate of 5.35% that is compounded quarterly (4 times) during the year. What is the Nominal Annual Interest Rate? |
| Effective Annual Interest Rate: | 5.35% |
| Npery (Number of compounding periods per year) | 4 |
| Nominal Annual Interest Rate: | 5.2459% | (ANSWER = 5.25%) |
| Example #13 | Calculating the Interest Rate per period of a loan or an investment |
| If you make monthly payments of 200 on an $8000 loan over 4 years, what is the Annual Interest Rate of the loan? |
| 4 | Years of the Loan |
| -200 | Monthly Payment |
| 8000 | Amount of the loan |
| Monthly Interest Rate of the Loan | 0.77% | (ANSWER = .77%) |
| Annual Interest Rate of the Loan | 9.24% | (ANSWER = 9.24%) |
| Note: Multiply the years of the loan by 12 months for the monthly rate |
| Note: Multiply the Monthly Interest Rate by 12 to get the annual rate. |
| Example #14 | Calculating the Geometric Average Return (or Mean) for a range of data |
| A stock has produced returns of 14.6 percent, 5.3 percent, 17.6 percent, and -4.7 percent over the past four years, respectively. What is the geometric average return? |
| Year 1 | 1.146 | Add 1 to all positive returns |
| Year 2 | 1.053 | Add 1 to all positive returns |
| Year 3 | 1.176 | Add 1 to all positive returns |
| | 0.953 | For negative returns, subtract it from 1. You have to do this to keep all data positive. |
| | 7.84% | (ANSWER = 7.84%; Note: Place a minus 1 after the formula to get rid of the whole number) |
| EXAMPLE #15: | SIMPLE MATH CALCULATIONS |
| | 2 |
| | 2 |
| | 5 |
| Adding cell B169 to cell B170: | 4 |
| Subtracting cell B169 from cell B170 | 0 |
| Multiplying cell B169 by cell B170 | 4 |
| Dividing cell B170 by cell B169 | 1 |
| Using Parenthesis: Multiplying cell B169 by (cell B170 + cell B171) | 14 |
| Calculating cell B169 to the power of cell B170 | 4 |
| Calculating the Square Root of cell B177: | 2 |
| Calculating the Natural Logarithm of cell B177 | 1.3863 |
gornmop
excel_examples.xls
