Introduction to Financial Management Project
Objective: This project is designed for you to acquire the skills of using Excel spreadsheets, and to apply the concepts of time value of money and solve the problems.
Requirement: Create a worksheet in Excel, and use the following syntax to answer 9 questions. Each student has to complete 9 questions. Your worksheet must show answers from typing in the syntax and arguments.
|
Purpose
|
Function
|
|
Find the future value
|
=fv(rate,nper,pmt,[pv],type)
|
|
Find the present value
|
=pv9rate,nper,pmt,[fv],type)
|
|
Payment
|
=pmt9rate,nper,pv,[fv],type)
|
|
Number of periods
|
=nper9rate,pmt,pv,[fv],type)
|
|
Yield (Interest rate)
|
=rate9nper,pmt,pv,[fv],type)
|
There are five arguments in each function. Rate is the interest rate per period. For example, if the interest rate per period is 5%, you will type .05 for this argument. Nper is the total number of periods. Pv is the present value, and fv is the future value. Pmt is the dollar amount of the periodic payment. The "type" argument tells Excel whether the cash flows occur at the end (0) or beginning (I) of the period. The bracket, "[]", means that you will input a negative value in order to return a positive value for the answer.
Example: Sue now has $125. How much would she have after 8 years if she leaves it invested at 8.5% with annual compounding?
Answer: In a cell of the Excel spreadsheet, type =fv(.085,8,0,-125,0).
Excel will return the answer $240.08.
Questions -
1. Your uncle has $375,000 and wants to retire. He expects to live for another 20 years and to earn 7% on his invested funds. How much could he withdraw at the end of each of the next 20 years and end up with zero in the account?
2. You want to go to Europe 5 years from now, and you can save $3,100 per year, beginning one year from today. You plan to deposit the funds in a mutual fund that you think will return 8.5% per year. Under these conditions, how much would you have just after you make the 5th deposit, 5 years from now?
3. Suppose the U.S. Treasury offers to sell you a bond for $3,000. No payments will be made until the bond matures 15 years from now, at which time it will be redeemed for $5,000. What interest rate would you earn if you bought this bond at the offer price?
4. Suppose you have $1,800 and plan to purchase a 5-year certificate of deposit (CD) that pays 2.5% interest, compounded annually. How much will you have when the CD matures?
5. What's the present value of $4,500 discounted back 5 years if the appropriate interest rate is 4.5%, compounded semiannually?
6. Your aunt is about to retire, and she wants to sell some of her stock and buy an annuity that will provide her with income of $50,000 per year for 30 years, beginning a year from today. The going rate on such annuities is 7.25%. How much would it cost her to buy such an annuity today?
7. Bob has $2,800 invested in a bank that pays 5% annually. How long will it take for his funds to double?
8. How much would $5,000 due in 25 years be worth today if the discount rate were 5.5%?
9. Morin Company's bonds mature in 8 years, have a par value of $1,000, and make an annualcoupon interest payment of $65. The market requires an interest rate of 8.2% on these bonds. What is the bond's price?