1.67PM Wed Nov 6Spreadsheet 2 copyK CHomeInsertDrawFormulasDataReviewVieCalibri (Body)BI UEBA711fxABCDEFInterest rate Monthly Deposit$275.00Computed Amount Target Amount Amount of Time Monthly interest rate Differ($19,186.76)126%$20,00060 months0.005$20,000($19,667.68)($19,944.95)($20,001.00)($20,208.24)37%$275.0060 months0.00583333347.50%$275.00$20,00060 months0.00625$20,000$20,0007.61%$275.0060 months0.00634166768%$275.0060 months0.003636364789101112131415161718

Question
Asked Nov 9, 2019

     “Suppose that your parents are willing to lend you $20,000 for part of the cost of your college education and living expenses.  They want you to repay them the $20,000 without any interest, in a lump sum 15 years after you graduate, when they plan to retire and move.  Meanwhile, you will be busy repaying federally guaranteed loans for the first 10 years after graduation.  But you realize that you won’t be able to repay the lump sum without saving up.  So you decide that you will put aside money in an interest-bearing account every month for the first five years before the payment is due.  You feel comfortable with setting aside $275 a month (the amount of the payment on your college loans, which will be paid off after 10 years).

     How high an annual nominal rate on savings do you need to accumulate the $20,000, in 60 months, if interest is compounded monthly?  Enter into a spreadsheet the values of d = 275, r = 0.05 (annual rate), and n = 60, and the savings formula with r replaced by r/12 (the monthly interest rate).  You will find that the amount accumulated is not enough.  Change r to 0.09, it’s more than enough.  Try other values until you determine r to two decimal places.”

     I entered information in Excel spreadsheet and I am trying to see if I am doing it correctly.  I have never used Excel.

 

 

 

1.67PM Wed Nov 6
Spreadsheet 2 copy
K C
Home
Insert
Draw
Formulas
Data
Review
Vie
Calibri (Body)
B
I U
EB
A
7
11
fx
A
B
C
D
E
F
Interest rate Monthly Deposit
$275.00
Computed Amount Target Amount Amount of Time Monthly interest rate Differ
($19,186.76)
1
2
6%
$20,000
60 months
0.005
$20,000
($19,667.68)
($19,944.95)
($20,001.00)
($20,208.24)
3
7%
$275.00
60 months
0.005833333
4
7.50%
$275.00
$20,000
60 months
0.00625
$20,000
$20,000
7.61%
$275.00
60 months
0.006341667
6
8%
$275.00
60 months
0.003636364
7
8
9
10
11
12
13
14
15
16
17
18
help_outline

Image Transcriptionclose

1.67PM Wed Nov 6 Spreadsheet 2 copy K C Home Insert Draw Formulas Data Review Vie Calibri (Body) B I U EB A 7 11 fx A B C D E F Interest rate Monthly Deposit $275.00 Computed Amount Target Amount Amount of Time Monthly interest rate Differ ($19,186.76) 1 2 6% $20,000 60 months 0.005 $20,000 ($19,667.68) ($19,944.95) ($20,001.00) ($20,208.24) 3 7% $275.00 60 months 0.005833333 4 7.50% $275.00 $20,000 60 months 0.00625 $20,000 $20,000 7.61% $275.00 60 months 0.006341667 6 8% $275.00 60 months 0.003636364 7 8 9 10 11 12 13 14 15 16 17 18

fullscreen
check_circleExpert Solution
Step 1

Computation:

...
L14
B
C
E
F
Trial and error method
Interest rate Monthly deposit Period (months) Target amount Monthly interest rate
2
275
6.00%
60
19,186.76
0.005
7.00%
-275
60
19,688.05
19,944.95
z 20,002.04
0.005833333
4
-275
7.50%
60
0.00625
5
0.006341667
7.61%
-275
60
6
8.00%
-275
60
20,206.14
0.006666667
7
8
10
help_outline

Image Transcriptionclose

L14 B C E F Trial and error method Interest rate Monthly deposit Period (months) Target amount Monthly interest rate 2 275 6.00% 60 19,186.76 0.005 7.00% -275 60 19,688.05 19,944.95 z 20,002.04 0.005833333 4 -275 7.50% 60 0.00625 5 0.006341667 7.61% -275 60 6 8.00% -275 60 20,206.14 0.006666667 7 8 10

fullscreen

Want to see the full answer?

See Solution

Check out a sample Q&A here.

Want to see this answer and more?

Solutions are written by subject experts who are available 24/7. Questions are typically answered within 1 hour*

See Solution
*Response times may vary by subject and question
Tagged in

Business

Finance

Banking Industry

Related Finance Q&A

Find answers to questions asked by student like you

Show more Q&A add
question_answer

Q: New parents wish to save for their newborn's education and wish to have $46,000 at the end of 17 yea...

A: Computation of yearly payment:Hence, the yearly payment is -$1,682.02.

question_answer

Q: You borrow $5000 at 10% per year and will pay off the loan in 3 equal annual payments starting one y...

A: Calculation of Payment at the end of Year 2:The interest payment is $348.94 and principal payment is...

question_answer

Q: The next dividend payment by Grenier, Inc., will be $1.64 per share. The dividends are anticipated t...

A: Question 1:Calculation of Dividend Yield and Capital Gains Yield:The dividend yield is 5.29% and cap...

question_answer

Q: Scenario Probability Stock fund Rate of Return Bond fund Rate of Return Severe recession 0.05 -30 ...

A: Computation of variance and standard deviation of stock fund:

question_answer

Q: What would the average tax rate for a person who paid taxes of $6,435 on taxable income of $40,780?

A: Calculation of average tax rate:Answer: Average tax rate is 15.78%Meaning: Average tax rate is calcu...

question_answer

Q: Is there an easily identifiable debt-equity ratio that will maximize the value of a firm? Why or why...

A: The value of the firm is dependent upon the future cash flows and the discount rate. In the absence ...

question_answer

Q: Compute Bowling Avenue Inc.'s required rate of return given a beta of.9, risk free rate of 3.25%, an...

A: Calculation of required rate of return:Answer:The required rate of return is 8.425%

question_answer

Q: Fran Powers created following budget and reported actual spending listed. Calculate the variance for...

A: Variance:The variance can be defined as the anticipation of the squared unconventionality of a rando...

question_answer

Q: You own $25,080 of Human Genome stock that has a beta of 3.72. You also own $13,376 of Frozen Food E...

A: To determine the portfolio beta we first need to calculate the weights of each investment.