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

 

[BadgerRoad]

Actually, mortgages in the US are compounded monthly which is what your example is assuming. You have a 25 year loan which is 300 months. Your interest rate is 6.4%/12=.533333% which is a monthly percent.
When you say Canada uses semi-annual compounding what does that mean? Please provide an example.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: PV Formula: Compounded Semi-Annually (Mortgage)
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Dave Patton]

XL2BB is not on this system
=PV((1+O7/2)^(2/12)-1,O6*12,-O5,0,0)

Payment	61,347.00​
Term	25​
Rate	6.4%​
PV	$9,242,158.78 ​

 

[Dave Patton]

Mortgage - Canadian.xlsm
	A	B
1
2
3	Amount	$9,242,158.78
4	Rate	6.4%
5	Term	25
6	Payment	$61,347.00
7
8
9	Payment	61,347.00
10	Term	25
11	Rate	6.4%
12
13	PV	$9,242,158.78
14
2a

Cell Formulas
Range	Formula
B3	B3	=B13
B4	B4	=B11
B6	B6	=PMT((1+B4/2)^(2/12)-1,B5*12,-B3,0,0)
B13	B13	=PV((1+B11/2)^(2/12)-1,B10*12,-B9,0,0)

 

[JustAdam8]

Thank you VERY much Dave Patton for your help! Very easy to understand and I appreciate you taking the time to assist.

Sorry for the cross-platform Fluff! I did not realize and won't do it again. Thank you everyone,

Adam

 

[Poneshow]

If you were looking for the formula to spit out in a colum what Principal & Interest is on the monthly payment, what would that formula look like? Can't seem to figure it out.

 

[Dave Patton]

Welcome to the forum.

N.B. This post relates to a Canadian Mortgage. "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?"

The interest factor is shown in post #5.
You can prepare an amortization schedule that that shows the interest and principal part of each payment.

If your question is similar to the above, you can provide full information on your mortgage or
you can start your own thread with the relevant information.

 

[Poneshow]

Yes, In need of the Canadian Calculation. Currently I have this structure in place to Calculate the monthly principal and interest payment. Looking for the calculation that would print out just the interest only payment in a semi-annual compounding calculation

B7 = Interest Rate
B15 = Mortgage Amount
360 being the amortization period

=-PMT((B7%/2+1)^(1/6)-1,360,B15)

 

[Dave Patton]

Adjust the references and amounts to your information.

Mortgage - Canadian.xlsm
	A	B	C	D	E	F
1	Start Date	1-Jan-23
2	Principal	500,000.00
3	Rate	6.45%	0.53%
4	Term	30
5	Payment	$3,115.40	$3,115.40
6
7
8	Payment Num	Date	Payment	Interest	Extra Payments	Balance
9	0	1-Jan-23				500,000.00
10	1	1-Feb-23	3,115.40	2,651.22		499,535.82
11	2	1-Mar-23	3,115.40	2,648.76		499,069.18
Example_

Cell Formulas
Range	Formula
C3	C3	=(1+B3/2)^(2/12)-1
B5	B5	=PMT(C3,B4*12,-B2)
C5	C5	=PMT((1+B3/2)^(2/12)-1,B4*12,-B2)
A9:A369	A9	=SEQUENCE(B4*12+1,,0,1)
B9	B9	=B1
B10:B11	B10	=EDATE(B9,1)
C10:C11	C10	=MIN($B$5,F9+D10)
D10:D11	D10	=ROUNDUP(F9*$C$3,2)
F9	F9	=B2
F10:F11	F10	=F9-C10+D10-E10
Dynamic array formulas.