Question

Calculate a 30 year amortization schedule in excel for the purchase of an office building. Here are the details:

The building costs $250,000.

You are able to obtain a mortgage loan for 90% of the value.

You must come up with cash for the remaining 10%

The mortgage loan is a fixed rate loan at 6% interest per year using simple interest with a 30/360 formula.

Problem 1 - Calculate the monthly payment of the loan.

Problem 2 - Prepare a detailed amortization schedule month by month for 30 years showing the interest for each month, the remaining balance of the loan for each month of the term.

Problem 3 - Summarize the interest paid per year

Problem 4 - Summarize the principal per year.

Extra Credit Points - 25

For extra credit, calculate the following:

On the 1st month after Year 5 the company pays an additional $10,000 in principal.

On the 1st month after the 10 year the company pays an additional $30,000 in principal.

On the 1st day after the 15th year the company pay an additional $20,000.

Extra Credit Problem 1 - Calculate the net change in total interest paid from the original amortization to the new amortization schedule with the additional principal repayments.

Extra Credit Problem 2 - Calculate the new date when the loan will be finally paid off.

Extra Credit Problem 3 - Calculate the total revised number of payments with the new 3 additional principal payments

I just need the extra credit section done. Can you also show this on a spreadsheet and please list the formulas.

Thanks!

Answer 1

Parameters
Cost of Building	$	250000
Mortgage Loan Value	$	225000
Cash	$	25000
Tenure		30	years
Rate of Interest		6%	p.a.
Per month Rate	30/360	0.5%

Formula
Monthly Payment (EMI) =PMT(Annual Rate/360*30,Payment Period in Years*12,-Present Value of Loan,0)
Interest = Pending loan* Annual rate of Interest/360*30

Due to words limit full payment schedule could not be pasted

Problem 1	EMI	1349
Problem 2 :Payment Schedule for 30 Years
		Total	4,85,636	2,25,000	2,60,636
		Outstanding Loan - (b)	(a)	(b)	(c )
Year	Month	Outstanding Loan	EMI	Principal	Interest
1	1	2,25,000	1,349	224	1,125
1	2	2,24,776	1,349	225	1,124
1	3	2,24,551	1,349	226	1,123
1	4	2,24,325	1,349	227	1,122
1	5	2,24,097	1,349	229	1,120
1	6	2,23,869	1,349	230	1,119
1	7	2,23,639	1,349	231	1,118
1	8	2,23,408	1,349	232	1,117
1	9	2,23,176	1,349	233	1,116
1	10	2,22,943	1,349	234	1,115
1	11	2,22,709	1,349	235	1,114
1	12	2,22,474	1,349	237	1,112
2	1	2,22,237	1,349	238	1,111
2	2	2,21,999	1,349	239	1,110
2	3	2,21,760	1,349	240	1,109
2	4	2,21,520	1,349	241	1,108
2	5	2,21,279	1,349	243	1,106
2	6	2,21,036	1,349	244	1,105
2	7	2,20,792	1,349	245	1,104
2	8	2,20,547	1,349	246	1,103
2	9	2,20,301	1,349	247	1,102
2	10	2,20,053	1,349	249	1,100
2	11	2,19,805	1,349	250	1,099
2	12	2,19,555	1,349	251	1,098

Problem 3 & 4: Principal & Interest Paid Year Wise
	2,25,000	2,60,636
Year	Principal Paid	Interest Paid
1	2,763	13,425
2	2,933	13,254
3	3,114	13,073
4	3,306	12,881
5	3,510	12,677
6	3,727	12,461
7	3,957	12,231
8	4,201	11,987
9	4,460	11,728
10	4,735	11,453
11	5,027	11,161
12	5,337	10,851
13	5,666	10,522
14	6,016	10,172
15	6,387	9,801
16	6,781	9,407
17	7,199	8,989
18	7,643	8,545
19	8,114	8,074
20	8,615	7,573
21	9,146	7,042
22	9,710	6,478
23	10,309	5,879
24	10,945	5,243
25	11,620	4,568
26	12,337	3,851
27	13,098	3,090
28	13,906	2,282
29	14,763	1,425
30	15,674	514

Credit Question 1

Interest in 1st Scenario			2,60,636
Interest in 2nd Scenario			1,68,288
Interest Difference			92,348

Credit Question 2

Period Difference
Scenario 1			30 Years
Scenario 2			21 Years 8 Months
Difference			8 years 4 Months

Credit Question 3

Revised Installments are 247 full instalment and 1 last balance payout