Create a spreadsheet that you can use to estimate equivalent cash flows for an arbitrary sequence of cash flows over 50 periods. You want to be able to use this spreadsheet to convert an arbitrary sequence of cash flows into a present worth PW, a future worth FW at any time t, or an annual worth AW over N periods. You want to be able to do this using both discrete and continuous compounding factors, and you want to be able to compute effective interest rates. Take some care in designing your spreadsheet so you can easily do sensitivity analysis on interest rates and N, and so you can easily print out a compact and attractive report showing results.
Test your spreadsheet:
a. Salvage value: You are involved with a project that is expected to last 50 years and have a salvage value of $10 million. A consultant has advised your company that an expenditure of only $100,000 at the end of every 5 years will double the salvage value. Your company generally uses a MARR of 15%—do you buy the consultant’s recommendation?
b. Bonds: A bond has an initial purchase price of $1,000 and an interest rate of 6% paid at the end of each year for 30 years. At the end of year 30, the bond is redeemed and the owner is repaid the initial payment of $1,000.
i. What is the value of the bond at the beginning of year 6 to someone with a MARR of 7% At the beginning of year 29?
ii. What is the value of the bond at the beginning of year 6 to someone with a MARR of 5% At the beginning of year 29?