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.
Recent Comments