Exercise 13: Your First Excel Security1

You have been given a financial security that pays $10 per year for 8 years. If the discount rate is 9% what is the security worth?

a. Set this up in Excel with the ability to change the discount rate and the annual payment. Please do it two ways. Use a formula approach where each payment is discounted: PV = Paymentt, (1+K)t where t is the index for years (1, 2...8), K is the discount rate (9%), and Paymentt is the payment in year t ($10 each year). Use an Excel function: NPV() b. Assuming that you could buy a similar security (same payment stream) for $56, what is the IRR for this investment? c. Get the value using the PV() excel function. d. If you could invest each payment at 8% compounded annually, how much would you have at the end of 8 years? e. Build a data table showing the relationship between the value and the discount rate. f. Build a data table showing the relationship between the value, the discount rate, and the annual payment.