PV Formula: Compounded Semi-Annually (Mortgage)

[JustAdam8]

Hello. First post here. I hope you are all doing well! I am stuck on an issue and I was hoping someone could assist.

I am working through a formula to determine how much debt someone could potentially get if they know how much they could afford to pay monthly. The formula I am using is:

=PV(rate, nper, pmt)

RATE = interest rate per period
NPER = periods per term
PMT = payment per month


This works assuming a standard mortgage that is compounded annually (standard in the US). However, mortgages in Canada are compounded semi-annually by law. Does anyone know how I can adjust the formula above to account for the change?

Thank you!

+	A	B
1	Payment Per Month (PMT)	-61,347
2	Years	25
3	Periods Per Month (NPER)	300
4	Annual Interst Rate	6.40%
5	Rate Per Period (RATE)	0.533333%
6
7		$9,170,340.66

 

[Dave Patton]

Please ignore the extra formulas that were included in my post above.
If you prefer, you can remove the rounding.

You can recap the interest for years as follows

Mortgage - Canadian.xlsm
	H	I	J	K	L	M	N
2	Years		1	2	3	4	5
3	Cumulative interest		31,649.24	31,273.46	30,873.05	30,446.42	29,991.83
4
Example_

Cell Formulas
Range	Formula
J3	J3	=-CUMIPMT(C3,B4*12,B2,1,12,0)
K3	K3	=-CUMIPMT(C3,B4*12,B2,13,24,0)
L3	L3	=-CUMIPMT(C3,30*12,B2,25,36,0)
M3	M3	=-CUMIPMT(C3,30*12,B2,37,48,0)
N3	N3	=-CUMIPMT(C3,30*12,B2,49,60,0)

 

[Poneshow]

This is what I am trying to build.

Mortgage	Rate	Payment	Frequency	Int Only
$550,000.00	9.20%	$4,138.05	Monthly	Y
$500,000.00	9.20%	$3,761.67	Monthly	Y
$450,000.00	9.20%	$3,385.68	Monthly	Y
$400,000.00	9.20%	$3,009.49	Monthly	Y
$350,000.00	9.20%	$2,633.30	Monthly	Y
$300,000.00	9.20%	$2,257.12	Monthly	Y
$250,000.00	9.20%	$1,880.93	Monthly	Y
$200,000.00	9.20%	$1,504.74	Monthly	Y
$150,000.00	9.20%	$1,128.56	Monthly	Y
$100,000.00	9.20%	$752.37	Monthly	Y

Not trying to build an AM schedule or anything like that - trying to basically plug the Mortgage amount and Rate and then it outputs the proper interest only payment based on a Canadian compounded semi-annually interest only payment payment

 

[Dave Patton]

What results do you expect with one of lines that you show?
What does the "Interest Only" mean?
The interest amount normally varies with each payment.
You have not provided complete information on the mortgage and how they are to be calculated.

 

[Dave Patton]

You apparently have the information that you require.
You are showing the interest amount.

Choose the version of the formula that you prefer.

Mortgage - Canadian.xlsm
	A	B	C	D	E	F
2	Principal	550,000.00
3	Rate	9.20%	0.75%
4	Term	30
5	Payment				Interest	4,138.05
6						4,138.05
7						4,138.05
Example_

Cell Formulas
Range	Formula
C3	C3	=(1+B3/2)^(2/12)-1
F5	F5	=B2*C3
F6	F6	=B2*((1+B3/2)^(2/12)-1)
F7	F7	=B2*((1+0.092/2)^(2/12)-1)