Global Financial ManagementRivier UniversityModule 2 Problem Set TVMDirections: Using the financial functions of Excel, the Excel wizard and/or the formulas for TVM, solve each ofthe problems.Example A: You have $300,000 that you want to invest in a one year Certificate of Deposit (CD) with a 4%annual interest rate. What will be the value of that CD in a year?PV =$300,000.00VYR =4%N =Formula:FV = PV(1+1)^N =$312,000.00Wizard (FV):$312,000.00Excel Function:$312,000.00Example B: What if the investment made above were held in a CD for 5 years, with the same principleand interest rate?PV =$300,000.00VYR =4%N =5Formula:FV = PV(1+1)^N =$364,995.87Wizard (FV):$364,995.87Excel Function:$364,995.87Example C: For this scenario, lets assume that CDs are being offered for 5 years at a rate of 3%, 4% and5%. Using the table function of Excel, create a table that shows the FV at 3%, 4% and 5% for 0, 1, 2, 3, 4,and 5 years.How to use the table function in Excel: Set up the table below. For the dollar amount under the Column Years,use the Future value of your investment at the end of the time period. For this example, that is the amountcalculated above for $300,000 at 4% for 5 years, Cell E25. Highlight the information for Cells B39 through E45.On the tool bar, click on data, What-If Analysis, Data Table. In the dialog box, for Row input cell enter the timeperiod or I/Yrs. (D23 in this example). For Column input cell, enter the time period, N (cell D24 for this example).Years:Interest Rate$364,995.874%3%O $300,000.001 $309,000.005%|$300,000.00$315,000.00$330,750.00$300,000.00$312,000.002 $318,270.003 $327,818.104 $337,652.645 $347,782.22$324,480.00$337,459.20$347,287.50$364,651.88$382,884.47$350,957.57$364,995.87#1 - You plan to invest $100,000 in a 3 year Certificate of Deposit that has a 5% compound interest rate.What is its future value?PV =VYR =N =FV = PV(1+1)^N=#2 - You expect to receive a payment of $1 million in a year. That annual interest rate is 5%. What is thepresent value of the future payment?FV=VYR =N =PV = FV/(1+1)^N = # 2 - You expect to receive a payment of $1 million in a year. That annual interest rate is 5%. What is thepresent value of the future payment?FV=I/YR =N =PV = FV/(1+1)^N =#3 - You purchase two annuities. The first is for three years and pays $1500 annually. The second is forfour years and pays $2000 annually. The interest rate for both is 4%. What is the Future Value of thisportfolio?Investment #1Investment #2PaymentI/YR =N =FVA = PMT*[(1+i)^N-1))/i]Portfolio Future Value Total:# 4 - Pencil Corp is looking to issue a bond to raise capital for expanding their product line to includemechanical pencils. The company needs to raise $250,000 which will be paid back over a 10 year timeframe. Bond rates can be issued at 3%, 6% or 7%, with a higher assurance of investors with the higherrate. If a high enough rate is not offered, Pencil Corp runs the risk of not raising the necessary capital.However, the CFO does not want to spend more in bond interest than necessary, and wants repaymentamounts to fit within budget for the expansion project. Create an amortization table for the expense.How much would be paid in interest for each interest rate scenario? What rate would you choose andwhy? (Hint: Determine future value first)PV =$250,000.00I/YR =6%N =10FV = PV(1+1)^N =Years:Interest Rate3%6%7%136.78910Interest Paid:

Question

Accepted Answer

“Hi There, thanks for posting the question. But as per Q&A guidelines, we must answer the first…