Question

Given the following information:

Interest Rate (yearly): 3.5%

Mortgage: $250,000

Term (in months): 360

Start Date: October 1, 2019

Yearly Bonus: $10,000

Formulas to incorporate:

PMT

EDATE

SUM

Other Notes:

Do amortization monthly.

Note that the interest rate is yearly and that your amortization table is monthly.

Anchoring cells will make it faster to develop your spreadsheet.

You’ll want to set up your spreadsheet to have the following columns: Starting Balance, Interest Accrued, Payment, Principal, Ending Balance, Cumulative Interest, Cumulative Principal.

Case:

What is the minimum payment each month?

Scenarios (complete each scenario on a separate tab and label each tab appropriately):

1. Pay the minimum required.
2. Pay an extra $250/month. When will you have the loan paid off by?
3. Pay an extra $250/month and make a larger payment (equal to 50% of your year end bonus) each December. When will you have the loan paid off by?

Answer 1

(a)

Interest Rate (yearly)	3.50%
Mortgage	2,50,000
Term (in months)	360
Start Date	01-10-2019
Yearly Bonus	10,000
	EDATE(Last Payment Date,1)		start balance *3.5%/12	PMT(3.5%/12,360,250000,0,0)	Payment-interest accrued	Start balance-Principal
Month	Date	Starting Balance	Interest Accrued	Payment	Principal	Ending Balance	Cumulative Interest	Cumulative Principal
1	31-10-2019	2,50,000	729.17	-1122.61	393.45	249606.55	729.17	393.45
2	30-11-2019	249606.55	728.02	-1122.61	394.59	249211.96	1457.19	788.04
3	31-12-2019	249211.96	726.87	-1122.61	395.74	248816.22	2184.05	1183.78

This will continue for 360 months. Minimum Payment is $1122.61

(b) Pay extra $250 per month:

	EDATE(Last Payment Date,1)		start balance *3.5%/12	PMT(3.5%/12,360,250000,0,0)+250	Payment-interest accrued	Start balance-Principal
Month	Date	Starting Balance	Interest Accrued	Payment	Principal	Ending Balance	Cumulative Interest	Cumulative Principal
1	31-10-2019	2,50,000	729.17	-1372.61	643.45	249356.55	729.17	643.45
2	30-11-2019	249356.55	727.29	-1372.61	645.32	248711.23	1456.46	1288.77

This will continue and the Loan will be paid off by 261st month i.e on 30-06-2041 as can be seen below:

260	31-05-2041	1558.32	4.55	-1372.61	1368.07	190.26	107069.30	249809.74
261	30-06-2041	190.26	0.55	-1372.61	1372.06	-1181.80	107069.86	251181.80
262	31-07-2041	-1181.80	-3.45	-1372.61	1376.06	-2557.86	107066.41	252557.86

(c)  Pay an extra $250/month and make a larger payment (equal to 50% of your year end bonus) each December:

	EDATE(Last Payment Date,1)		start balance *3.5%/12	PMT(3.5%/12,360,250000,0,0)+250+if(month(Date)=12,10000*50%,0)	Payment-interest accrued	Start balance-Principal
Month	Date	Starting Balance	Interest Accrued	Payment	Principal	Ending Balance	Cumulative Interest	Cumulative Principal
1	31-10-2019	2,50,000	729.17	-1372.61	643.45	249356.55	729.17	643.45
2	30-11-2019	249356.55	727.29	-1372.61	645.32	248711.23	1456.46	1288.77
3	31-12-2019	248711.23	725.41	-6372.61	5647.20	243064.03	2181.86	6935.97

This will continue and the Loan will be paid off by 179th month i.e on 31-08-2034 as can be seen below:

178	31-07-2034	2613.65	7.62	-1372.61	1364.99	1248.66	70573.55	248751.34
179	31-08-2034	1248.66	3.64	-1372.61	1368.97	-120.31	70577.19	250120.31
180	30-09-2034	-120.31	-0.35	-1372.61	1372.96	-1493.27	70576.84	251493.27

(I can't paste the full amortization chart due to limitation of characters. Kindly rate positively,if found helpful. )