Excel Amortization - Calculation help

[mazher]

Hi, Please can someone explain to me the logic of this amortization table as I am unable to figure out how the values of interest and principal are calculated are calculated

Aisha Property Limited 26.7.17.xlsx
	B	C	D	E	F	G	H	I	J
2	MONTHLY CAPITAL AND INTEREST PAYMENTS
3	Loan fixed for 15 years out of 25					Quotation Date
4						Time
5
6		Cost of funds		2.1300%
7		Margin		4.9500%
8		PLA		0.0000%
9		Total interest rate		7.0800%
10
11					INTEREST	CAPITAL	GROSS	Break	Break
12	PERIOD	START	END	PRINCIPAL	PAYMENT	PAYMENT	PAYMENT	Cost %	Cost
13	1	26 Jul 17	29 Aug 17	1,015,000.00	6,693.99	536.50	7,230.49	4.00%	40,600.00
14	2	29 Aug 17	26 Sep 17	1,014,463.50	5,509.79	1,720.70	7,230.49	4.00%	40,578.54
15	3	26 Sep 17	26 Oct 17	1,012,742.80	5,893.33	1,337.16	7,230.49	4.00%	40,509.71
16	4	26 Oct 17	27 Nov 17	1,011,405.64	6,277.92	952.57	7,230.49	4.00%	40,456.23
17	5	27 Nov 17	27 Dec 17	1,010,453.07	5,880.00	1,350.49	7,230.49	4.00%	40,418.12
18	6	27 Dec 17	26 Jan 18	1,009,102.58	5,872.15	1,358.34	7,230.49	4.00%	40,364.10
Schedule

I have just posted a sample of a few months.

Any help in solving this mystical interest and principal payments will be much appreciated.

Aisha Property Limited 26.7.17.xlsx
	N	O	P	Q	R
8				Years	25
9				Principal	1,015,000.00
10				Interest	7.08%
11			INTEREST	CAPITAL	GROSS
12			PAYMENT	PAYMENT	PAYMENT
13		1	£5,988.50	£1,237.19	£7,225.69
14		2	£5,981.20	£1,244.49	£7,225.69
15		3	£5,973.86	£1,251.83	£7,225.69
16		4	£5,966.47	£1,259.22	£7,225.69
17		5	£5,959.04	£1,266.65	£7,225.69
Schedule

Cell Formulas
Range	Formula
O13:O312	O13	=SEQUENCE(R8*12)
P13:P312	P13	=-IPMT(R10/12,O13#,R8*12,R9)
Q13:Q312	Q13	=-PPMT(R10/12,O13#,R8*12,R9)
R13:R17	R13	=P13+Q13
Dynamic array formulas.

I am using these formulas

Thanks in advance

I am

 

[StephenCrump]

It looks like your first table calculates interest on a days/365 basis:

	A	B	C	D	E	F	G	H	I	J
1
2		MONTHLY CAPITAL AND INTEREST PAYMENTS
3		Loan fixed for 15 years out of 25					Quotation Date
4							Time
5
6			Cost of funds		2.13%
7			Margin		4.95%
8			PLA		0.00%
9			Total interest rate		7.08%
10
11						INTEREST	CAPITAL	GROSS	Break	Break
12		PERIOD	START	END	PRINCIPAL	PAYMENT	PAYMENT	PAYMENT	Cost %	Cost
13		1	26 Jul 2017	29 Aug 2017	1,015,000.00	6,693.99	536.50	7,230.49	4.00%	40,600.00
14		2	29 Aug 2017	26 Sep 2017	1,014,463.50	5,509.79	1,720.70	7,230.49	4.00%	40,578.54
15		3	26 Sep 2017	26 Oct 2017	1,012,742.80	5,893.33	1,337.16	7,230.49	4.00%	40,509.71
16		4	26 Oct 2017	27 Nov 2017	1,011,405.64	6,277.92	952.57	7,230.49	4.00%	40,456.23
17		5	27 Nov 2017	27 Dec 2017	1,010,453.07	5,880.01	1,350.48	7,230.49	4.00%	40,418.12
18		6	27 Dec 2017	26 Jan 2018	1,009,102.59	5,872.15	1,358.34	7,230.49	4.00%	40,364.10
Sheet1

Cell Formulas
Range	Formula
F13:F18	F13	=ROUND(E$9*E13*(D13-C13)/365,2)
G13:G18	G13	=H13-F13
E14:E18	E14	=E13-G13

 

[mazher]

It looks like your first table calculates interest on a days/365 basis:

	A	B	C	D	E	F	G	H	I	J
1
2		MONTHLY CAPITAL AND INTEREST PAYMENTS
3		Loan fixed for 15 years out of 25					Quotation Date
4							Time
5
6			Cost of funds		2.13%
7			Margin		4.95%
8			PLA		0.00%
9			Total interest rate		7.08%
10
11						INTEREST	CAPITAL	GROSS	Break	Break
12		PERIOD	START	END	PRINCIPAL	PAYMENT	PAYMENT	PAYMENT	Cost %	Cost
13		1	26 Jul 2017	29 Aug 2017	1,015,000.00	6,693.99	536.50	7,230.49	4.00%	40,600.00
14		2	29 Aug 2017	26 Sep 2017	1,014,463.50	5,509.79	1,720.70	7,230.49	4.00%	40,578.54
15		3	26 Sep 2017	26 Oct 2017	1,012,742.80	5,893.33	1,337.16	7,230.49	4.00%	40,509.71
16		4	26 Oct 2017	27 Nov 2017	1,011,405.64	6,277.92	952.57	7,230.49	4.00%	40,456.23
17		5	27 Nov 2017	27 Dec 2017	1,010,453.07	5,880.01	1,350.48	7,230.49	4.00%	40,418.12
18		6	27 Dec 2017	26 Jan 2018	1,009,102.59	5,872.15	1,358.34	7,230.49	4.00%	40,364.10
Sheet1

Cell Formulas
Range	Formula
F13:F18	F13	=ROUND(E$9*E13*(D13-C13)/365,2)
G13:G18	G13	=H13-F13
E14:E18	E14	=E13-G13

Extremely thankful Stephen, I was using IPMT and PPMT functions.

Any idea why there is a difference of £4.80 (£7225.69 and £7230.49)?

 

[mazher]

I am unable to locate that difference of 4.80 in the instalment value calculated by t he formula and the one i have provided in the first table.

Any way thanks @StephenCrump or all your time and help.

 

[StephenCrump]

I am unable to locate that difference of 4.80 ....

That's just the difference between using days/365 and a simplistic assumption that all months are of equal duration.

Using a 12-month loan for example:

	A	B	C	D	E	F	G
1
2		Loan	100,000
3		Date	20 Jun 2022
4		Interest	5%	p.a.
5		Repayment	$8,560.75
6
7		Month		Start Amount	Interest	Repayment	End Amount
8			20 Jun 2022				100,000.00
9		1	20 Jul 2022	100,000.00	410.96	-8,561.10	91,849.86
10		2	20 Aug 2022	91,849.86	390.05	-8,561.10	83,678.81
11		3	20 Sep 2022	83,678.81	355.35	-8,561.10	75,473.06
12		4	20 Oct 2022	75,473.06	310.16	-8,561.10	67,222.12
13		5	20 Nov 2022	67,222.12	285.46	-8,561.10	58,946.48
14		6	20 Dec 2022	58,946.48	242.25	-8,561.10	50,627.63
15		7	20 Jan 2023	50,627.63	214.99	-8,561.10	42,281.52
16		8	20 Feb 2023	42,281.52	179.55	-8,561.10	33,899.97
17		9	20 Mar 2023	33,899.97	130.03	-8,561.10	25,468.90
18		10	20 Apr 2023	25,468.90	108.16	-8,561.10	17,015.96
19		11	20 May 2023	17,015.96	69.93	-8,561.10	8,524.79
20		12	20 Jun 2023	8,524.79	36.20	-8,560.99	0.00
Sheet1

Cell Formulas
Range	Formula
C5	C5	=PMT(i/12,12,-Amount)
C8:C20	C8	=EDATE(LoanDate,B8)
D9:D20	D9	=G8
E9:E20	E9	=ROUND(i*D9*(C9-C8)/365,2)
G8	G8	=Amount
G9:G20	G9	=SUM(D9:F9)

Named Ranges
Name	Refers To	Cells
Amount	=Sheet1!$C$2	G8, C5
i	=Sheet1!$C$4	C5, E9:E20
LoanDate	=Sheet1!$C$3	C8:C20

On a simplistic basis, the loan repayment is $8,560.75 per month.

On a days/365 basis, the repayments required are $8,561.10 for 11 months, and $8,560.99 in the 12th month.

But if the loan start date was, say 1 February 2022, the loan repayments would be $8,559.14 for 11 months and $8,559.03 residual.