In: Finance
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. 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% |
? |
? |
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
|
|
Portfolio balance (PV) |
-500000 |
expected return : r |
3.5 % |
period years |
30 |
Withdrawal annual amount (PMT) |
? |
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.
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