Question

In: Finance

Analysis to be done using Excel. (Note go over Time value of money recorded lecture and...

Analysis to be done using Excel. (Note go over Time value of money recorded lecture and also supplemental reading material on Time value OF money)

Required format on Excel is shown below for each assignment

  1. If you invest $10,000 today, and do not add any more , calculate future values for
    1. r = 1%; for 5 years and 10 years
    2. r = 2%; for 5 years and 10 years
    3. r = 3%; for 5 years and 10 years

1. Calculate Future Value ( FV)

?

Amount to be deposited (PV)

-10,000

growth rates : r

term (n) , years

term (n) , years

percent

5

10

1%

?

?

2%

?

?

3%

?

?

  1. If you put $10,000 (ten thousand) today, plus add $ 1000 (one thousand dollars) every year , calculate future value (FV) in 20 (Twenty years)   
    1. r = 1%; for 20 years
    2. r = 2%; for 20 years
    3. r = 3%; for 20 years

2. Calculate FV

?

Current value (PV)

-10,000

adding annual

-1,000

Period (n)

20

Interest rates : r

term (n) , years

percent

20

1%

?

2%

?

3%

?

If Julie has $500,000 (Half million) in her retirement account. If she invests in a conservative investment that will provide a return of 5 % a year, and if she wants her money to last 30 years, how much can she pull out at the end every year for next 30 years

  1. Withdrawal amount

Portfolio balance (PV)

-500000

expected return : r

3.5 %

period years

30

Withdrawal annual amount (PMT)

?

  1. John wants to borrow $200,000 (two hundred dollars) to buy a house. Using a 30 year mortgage with interest rate of 4 %. Calculate MONTHLY PAYMENTS (Please review the narrated PPT where I have covered how to address Monthly payments) and amount of interest paid over the life of the loan. He is also considering paying off the loan in in 15 years.
  • Calculate monthly PMT and how much interest amount ($$) will he pay over the life of the loan with a 30 year loan ?
  • Calculate monthly PMT and how much interest will he pay over the life of the loan if he paid of n 15 years

John has a store credit card with annual interest rate of 20 % (monthly: 20 % divided by 12 = 1.666667%). His current balance is $ 5000 and his minimum required monthly payment is $ 100. His friend told him that if he paid $ 150 ( instead of $100) it will reduce the amount of time (NPER) to pay by down the debt by more than fifty percent, and the total amount of interest he will pay will be reduced by almost 60 %.   And if he paid $200 the time to pay will be down by 70 % and total interest paid by almost seventy five percent….Do you believe that is true?

   Calculate (using excel and using excel formulas)   number of months to pay off, and total interest paid for monthly payment (PMT) of $ 100, $ 150 and $200 respectively. And confirm if John’s friend is correct with his assessment.

   Jill has a credit card balance of $10,000 with an annual interest rate of 15.00 %. She plans to pay $ 300 a month, and she also has a monthly charge from   her health club that automatically charges $ 50 per month towards her credit card. How long will it take her to pay off ( number of months)   her credit card balance ( assuming she does not charge anything more except for the health club fees.

Solutions

Expert Solution

1]

FV is calculated using FV function :

rate = r (rate of return)

nper = n (term in years)

pmt = 0 (there are no periodic deposits)

pv = -10,000 (initital amount deposited)

The formulas are below

The formulas are below

2]

FV is calculated using FV function :

rate = r (rate of return)

nper = n (term in years)

pmt = -1000 (periodic deposits)

pv = -10,000 (initital amount deposited)

The formulas are below

The formulas are below

3]

Withdrawal amount is calculated using PMT function in Excel :

rate = r (rate of return)

nper = n (term in years)

pv = amount invested in portfolio

Withdrawal annual amount is $32,525.72

4]

Monthly loan payment is calculated using PMT function in Excel :

rate = interest rate / 12   (converting annual rate into monthly rate)

nper = n*12 (n year loan with 12 monthly payments each year)

pv = (loan amount)

total interest paid (monthly payment * n * 12) - loan amount

The formulas are below

The formulas are below

The formulas are above

The formulas are above


Related Solutions

This should be done in as an Excel file. Using the simple time value of money...
This should be done in as an Excel file. Using the simple time value of money concepts from Corporate Finance course: Calculate the price of an annual coupon‐bond with a par value of $1,000, time to maturity of 10 years, coupon rate of 10% and yield to maturity of 12% (bond DC). yield to maturity of 8% (bond PC). Do the sensitivity analysis on the price of these two bonds (bond DC & bond PC) by changing the following (one...
Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and...
Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and Future Value Equations 4. If you invested $200 at 5%, how much would it be worth in 30 years? 5. How many years does it take to double your money if it is invested at 6%? 6. If you invest $10,000 in a 20 year annuity paying 5%, what would be the annual payment made to you? 7. If you have a student loan...
Solve the following financial problems using the time value of money functions in Excel (PV, FV,...
Solve the following financial problems using the time value of money functions in Excel (PV, FV, PMT, NPER, RATE, EFFECT) OR using your financial calculator. Assume you deposit $3,000 today, and $300 at the end of each year, in an account earning 4% per year for 20 years. What is the future value? General Electric has an unfunded pension liability of $300 million that must be paid in 15 years. The CFO deposits $10 million in account today to help...
Solve the following financial problems using the time value of money functions in Excel (PV, FV,...
Solve the following financial problems using the time value of money functions in Excel (PV, FV, PMT, NPER, RATE, EFFECT) OR using your financial calculator. Assume you deposit $3,000 today, and $300 at the end of each year, in an account earning 4% per year for 20 years. What is the future value? General Electric has an unfunded pension liability of $300 million that must be paid in 15 years. The CFO deposits $10 million in account today to help...
Advanced Time Value of Money Problems (Try to work this question WITHOUT using Excel, get calculation...
Advanced Time Value of Money Problems (Try to work this question WITHOUT using Excel, get calculation in detail) Question (Retirement planning) You have just graduated Hofstra University at age 22. You hard work has paid off as you already have a job as an investment banker at Goldman Sachs waiting for you. You plan to work continuously until age 65 and retire exactly on that day. You expect to live until exactly 90 and enjoy your golden years and leave...
Advanced Time Value of Money Problems (Try to work this question WITHOUT using Excel, get calculation...
Advanced Time Value of Money Problems (Try to work this question WITHOUT using Excel, get calculation in detail) Question (College planning) Your child was just born and you are planning for his/her college education. Based on your wonderful experience in Financial Economics you decide to send your child to Hofstra University as well. You anticipate the annual tuition to be $60,000 per year for the four years of college. You plan on making equal deposits on your child’s birthday every...
Can you use Excel for time value of money computations such as NPV or IRR? If...
Can you use Excel for time value of money computations such as NPV or IRR? If so, what are the functions? Please no handwritten answers
Managerial Finance Learning Objective: Calculate the time value of money Activity: Using the time value of...
Managerial Finance Learning Objective: Calculate the time value of money Activity: Using the time value of money (TVM) principles develop a retirement calculator using the following steps. Submit an Excel file showing your calculations and answers to the questions below. Step 1: Calculate the future value of the money you will need. How old are you now? 22 At what age do you want to retire? 60 Assume you will live until the age of 90. In today's dollars, how...
"Time Value of Money " The time value of money is a critical concept to understand...
"Time Value of Money " The time value of money is a critical concept to understand in accounting, especially when dealing with loans, investment analysis, and capital budgeting decisions. The time value of money concept can be used to decide which projects to start and what investments to make. You can also utilize the time value of money concept in your personal life. Provide two (2) decisions you may need to make that could involve the time value of money....
"Time Value of Money " The time value of money is a critical concept to understand...
"Time Value of Money " The time value of money is a critical concept to understand in accounting, especially when dealing with loans, investment analysis, and capital budgeting decisions. The time value of money concept can be used to decide which projects to start and what investments to make. You can also utilize the time value of money concept in your personal life. Provide two (2) decisions you may need to make that could involve the time value of money....
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT