simple canadian mortgage calculation

[anon125]

I started with a vertex42 free canadian mortgage spreadsheet.
See attached, -simplified!
i need to know how much principal is paid off during the say 1 to 5 years of the mortgage.
ideally in one cell! for each scenario
Interest is just the rate times the reducing principle - or principal?
so payment less interest will be the principle each month.
Suggestions on doing this without a table for each calculation!
thanks all
tried various formulae on the internet- none worked
CUMPRINC function etc just give nonsense numbers- maybe because it is a canadian mortgage!

as the xlbb? thing wont work here...you can download the spreadsheet here Mortgage calculation
NOTE: it is from a computer that NEVER connects to the internet.

 

[Dave Patton]

Please post your sample information and expected result with the forum's tool XL2BB.

What version of Excel are you using?

 

[anon125]

Dave- no can do. see last line of the post

 

[Dave Patton]

What amounts are involved?
Interest rate, term, etc.
Canadian interest rate?
So you want me to guess at everything? You want an answer??

The post shows the Principal and interest paid for 60 months and years 1 - 5

With Excel 365, I built an amortization for a mortgage.
The summary numbers agree to the amortization schedule.

Mortgage - Canadian.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Start Date date	24-Mar-23			Canadian mortgage		Year
2		Mortgage amount amt	$543,200.00					1	2	3	4	5
3		Annual rate	6.00%		55,207.02	Principal 60 months		9,775.95	10,371.30	11,002.91	11,672.99	12,383.87
4		Years term	25		153,318.75	Interest 60 months		31,929.21	31,333.85	30,702.24	30,032.17	29,321.28
5		Payments per year	12
6
7	Period	Date	Payment	Interest	Principal	Balance
8	1	24-Apr-23	3,475.43	2,682.66	792.77	542,407.23
9	2	24-May-23	3,475.43	2,678.74	796.69	541,610.54
Example

Cell Formulas
Range	Formula
C1	C1	=TODAY()
H3	H3	=-CUMPRINC(rCdn,300,C2,1,12,0)
I3	I3	=-CUMPRINC(rCdn,300,C2,13,24,0)
J3	J3	=-CUMPRINC(rCdn,300,C2,25,36,0)
K3	K3	=-CUMPRINC(rCdn,300,C2,37,48,0)
L3	L3	=-CUMPRINC(rCdn,300,C2,49,60,0)
H4	H4	=-CUMIPMT(rCdn,C4*C5,C2,1,12,0)
I4	I4	=-CUMIPMT(rCdn,C4*C5,C2,13,24,0)
J4	J4	=-CUMIPMT(rCdn,C4*C5,C2,25,36,0)
K4	K4	=-CUMIPMT(rCdn,C4*C5,C2,37,48,0)
L4	L4	=-CUMIPMT(rCdn,C4*C5,C2,49,60,0)
E3	E3	=-CUMPRINC(rCdn,300,C2,1,60,0)
E4	E4	=-CUMIPMT(rCdn,C4*C5,C2,1,60,0)
A7:F307	A7	=MortgageAmort(C1,C2,C3,C4)
Dynamic array formulas.

 

[anon125]

Thanks Dave
Can it be done without the A to F columns?

i will have lots of options on one page, so i dont want lots of 60 row data for each option.

 

[anon125]

here is my worksheet
well i tried to upload it but it said it as too large.
so i shrunk it, now i can see it on my chromebook, but this forum wont upload it

 

[anon125]

i had to go to a windows machine to resize it before your forum would accept it.
all other forums are better than this

 

[Dave Patton]

Can it be done without the A to F columns? Yes

Mortgage - Canadian.xlsm
	C	D
1		10%
2		0.0081648
3	Principal	100,000.00
4	Payments	$894.49
5	Principal months 1 -12	979.23
6	Interest months 1-12	9,754.62
Example1

Cell Formulas
Range	Formula
D2	D2	=(1+D1/2)^(2/12)-1
D4	D4	=PMT(D2,300,-D3)
D5	D5	=-CUMPRINC(D2,300,D3,1,12,0)
D6	D6	=-CUMIPMT(D2,300,D3,1,12,0)

 

[anon125]

Dave, Take a bow.
It works very well
For anyone else using it, it is based on 25 year amortisation
Thanks VERY much

 

[Dave Patton]

Thanks for the feedback.