Kay Kinder has borrowed $500,000 at a nominal annual rate with monthly compoundi
ID: 2650894 • Letter: K
Question
Kay Kinder has borrowed $500,000 at a nominal annual rate with monthly compounding of 6.50% to start a new company. The first payment on the loan will be at the end of year 1. Revenue of the new company is forecasted to be $50,000 in year 1 and be $100,0000 larger in each year thereafter. Required annual payments on the loan at the end of each year are to be 8% of the Revenue in that year. In what year will the loan balance go to zero? Show excel formula/calculations. Kay Kinder has borrowed $500,000 at a nominal annual rate with monthly compounding of 6.50% to start a new company. The first payment on the loan will be at the end of year 1. Revenue of the new company is forecasted to be $50,000 in year 1 and be $100,0000 larger in each year thereafter. Required annual payments on the loan at the end of each year are to be 8% of the Revenue in that year. In what year will the loan balance go to zero? Show excel formula/calculations.Explanation / Answer
Answer
Present value of loan : $ 500,000
Annual nominal interest rate : 6.5%
Monthly nominal Interest rate : 0.54167% (6.5%/12)
Loan repayment: Annual (at the end of each year)
Loan repayment Amount: 8% of the revenue in that year.
Month
Loan with interest
Revenue
Repayment
Closing
A
B
C
A-C
Amount of loan(n-1) month*1.0054167
(Yearly)
(Revenue*0.08)
Year 1
0
500000.00
1
502708.35
2
505431.37
3
508169.14
4
510921.74
5
513689.25
6
516471.75
7
519269.32
8
522082.05
9
524910.01
10
527753.29
11
530611.97
12
533486.14
50000
4000
529486.14
Month
Year 2
0
529486.14
1
532354.21
2
535237.81
3
538137.03
4
541051.96
5
543982.67
6
546929.27
7
549891.82
8
552870.42
9
555865.15
10
558876.10
11
561903.37
12
564947.03
1050000
84000
480947.03
Month
Year 3
0
480947.03
1
483552.18
2
486171.43
3
488804.88
4
491452.59
5
494114.64
6
496791.11
7
499482.08
8
502187.62
9
504907.82
10
507642.76
11
510392.50
12
513157.15
2050000
164000
349157.15
Month
Year 4
0
349157.15
1
351048.43
2
352949.95
3
354861.78
4
356783.96
5
358716.55
6
360659.61
7
362613.19
8
364577.36
9
366552.16
10
368537.67
11
370533.93
12
372541.00
3050000
244000
128541.00
Month
Year 5
0
128541.00
1
129237.26
2
129937.30
3
130641.14
4
131348.78
5
132060.26
6
132775.59
7
133494.79
8
134217.89
9
134944.91
10
135675.87
11
136410.78
12
137149.68
4050000
137149.68
0
324000
Answer : In year 5, the loan balance will go to zero.
Month
Loan with interest
Revenue
Repayment
Closing
A
B
C
A-C
Amount of loan(n-1) month*1.0054167
(Yearly)
(Revenue*0.08)
Year 1
0
500000.00
1
502708.35
2
505431.37
3
508169.14
4
510921.74
5
513689.25
6
516471.75
7
519269.32
8
522082.05
9
524910.01
10
527753.29
11
530611.97
12
533486.14
50000
4000
529486.14
Month
Year 2
0
529486.14
1
532354.21
2
535237.81
3
538137.03
4
541051.96
5
543982.67
6
546929.27
7
549891.82
8
552870.42
9
555865.15
10
558876.10
11
561903.37
12
564947.03
1050000
84000
480947.03
Month
Year 3
0
480947.03
1
483552.18
2
486171.43
3
488804.88
4
491452.59
5
494114.64
6
496791.11
7
499482.08
8
502187.62
9
504907.82
10
507642.76
11
510392.50
12
513157.15
2050000
164000
349157.15
Month
Year 4
0
349157.15
1
351048.43
2
352949.95
3
354861.78
4
356783.96
5
358716.55
6
360659.61
7
362613.19
8
364577.36
9
366552.16
10
368537.67
11
370533.93
12
372541.00
3050000
244000
128541.00
Month
Year 5
0
128541.00
1
129237.26
2
129937.30
3
130641.14
4
131348.78
5
132060.26
6
132775.59
7
133494.79
8
134217.89
9
134944.91
10
135675.87
11
136410.78
12
137149.68
4050000
137149.68
0
324000
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.