Question

Fred is repaying a loan of X at a 4% annual effective rate.

He makes payments of 100 at the end of each year for 10 years, followed by payments of 200 for n years.

The amount of interest in the 8^th payment is 60.

Determine X. Possible answers are: 1,140 or 1,970 or 1,360 or 1,740 or 1,870. Thanks

Answer 1

Solution: $1,740.

Mortgage Table:

Mortgage Table
Year	Balance	Interest Paid	Principal Paid	New Balance
1	$    1,740.08	$         69.60	$            30.40	$    1,709.69
2	$    1,709.69	$         68.39	$            31.61	$    1,678.07
3	$    1,678.07	$         67.12	$            32.88	$    1,645.20
4	$    1,645.20	$         65.81	$            34.19	$    1,611.00
5	$    1,611.00	$         64.44	$            35.56	$    1,575.44
6	$    1,575.44	$         63.02	$            36.98	$    1,538.46
7	$    1,538.46	$         61.54	$            38.46	$    1,500.00
8	$    1,500.00	$         60.00	$            40.00	$    1,460.00
9	$    1,460.00	$         58.40	$            41.60	$    1,418.40
10	$    1,418.40	$         56.74	$            43.26	$    1,375.14
11	$    1,375.14	$         55.01	$          144.99	$    1,230.14
12	$    1,230.14	$         49.21	$          150.79	$    1,079.35
13	$    1,079.35	$         43.17	$          156.83	$        922.52
14	$       922.52	$         36.90	$          163.10	$        759.42
15	$       759.42	$         30.38	$          169.62	$        589.80
16	$       589.80	$         23.59	$          176.41	$        413.39
17	$       413.39	$         16.54	$          183.46	$        229.93
18	$       229.93	$            9.20	$          190.80	$          39.12
19	$          39.12	$            1.56	$          198.44	$      -159.31

Formulas Used:

First, we prepare an amortization schedule model with the above-used formulas. The key part is to link everything with the mortgage amount, interest rate and the payments per period. Just assume a mortgage value of $1,000 here while preparing. Make sure you change the amount in principal paid to $200 after year 10 as required in the question. The key indicator of a correct loan amount, as per the question is the amount of interest in the 8^th payment is 60. So, we highlight these cells which will help us in determining our final answer.

Now, to find the final loan amount, we will use the "Goal Seek" option in the "What-if Analysis" in the "Data" tab. Goal Seek option lets you find the right input for the required output.

By putting in the following cell addresses and the set value to "60", you can get a result in the loan amount that makes that cell equal to 60:

Just press OK after this and you'll get the Loan value as $1740.

If this method doesn't work, you can always type in the options in the Loan amount cell and use the trial-and-error method to find the correct answer match.