Monthly Mortgage payments for a Semi annual compounding rate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel provides functions to calculate a variety of numbers for mortgages
(when they have a monthly compounding interest rate) but I cannot find
functions to generate those numbers when the quoted annual rate is compounded
on a semi annual basis.
 
These functions work on periods determined by you, take for example this
simplified PMT function:
PMT(rate,nper,pv)
Rate is the interest rate per period, Nper is the total number of payments
for the loan, Pv is the present value (amount to be paid back) also known as
the principal.

Pmt function for monthly payment:
=PMT(7.5%/12,360,100000)

where 7.5% is the annual interest rate, divided by 12 to apply the rate for
the period, which in this case is one month, total number of payments is 360
(or 30 years times 12 months) and the loan was for $100,000.

Same function, semi-annual payment:
=PMT(7.5%/2,60,100000)

where 7.5% is the annual interest rate, divided by 2 to apply the rate per
period, 60 is the total number of payments (30 years times 2 payments per
year) and the loan was for $100,000.

In other words, you determine the period in the function. Does that help?
If there is a specific function you would like more info on I'd be happy to
assist.
 
You'll have to establish an effective rate per month, taking into account semi-annual payments.
There have been many discussions here about the way one should do that. If your aim is to check or predict the calculations of
your mortgage company you'll have to get their calculation rules.
In the meantime you could experiment with the EFFECT() and NOMINAL() functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excel provides functions to calculate a variety of numbers for mortgages
| (when they have a monthly compounding interest rate) but I cannot find
| functions to generate those numbers when the quoted annual rate is compounded
| on a semi annual basis.
 
<Pmt function for monthly payment:
=PMT(7.5%/12,360,100000)>

That is exactly what the discussions I mentioned are about. Some take Rate/12, some use EFFECT or an equivalent (or even other)
formula to get to a monthly rate.
It depends on how the calculation rule was defined.

One thing to ask yourself before you give an answer too quickly is how the rate should be established if payments were continuous.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| These functions work on periods determined by you, take for example this
| simplified PMT function:
| PMT(rate,nper,pv)
| Rate is the interest rate per period, Nper is the total number of payments
| for the loan, Pv is the present value (amount to be paid back) also known as
| the principal.
|
| Pmt function for monthly payment:
| =PMT(7.5%/12,360,100000)
|
| where 7.5% is the annual interest rate, divided by 12 to apply the rate for
| the period, which in this case is one month, total number of payments is 360
| (or 30 years times 12 months) and the loan was for $100,000.
|
| Same function, semi-annual payment:
| =PMT(7.5%/2,60,100000)
|
| where 7.5% is the annual interest rate, divided by 2 to apply the rate per
| period, 60 is the total number of payments (30 years times 2 payments per
| year) and the loan was for $100,000.
|
| In other words, you determine the period in the function. Does that help?
| If there is a specific function you would like more info on I'd be happy to
| assist.
|
| "Iain" wrote:
|
| > Excel provides functions to calculate a variety of numbers for mortgages
| > (when they have a monthly compounding interest rate) but I cannot find
| > functions to generate those numbers when the quoted annual rate is compounded
| > on a semi annual basis.
 
BoniM, thanks for your reply. I use these functions
(PMT(rate,nper,pv,fv,type)CUMIPMT(rate,nper,pv,start_period,end_period,type)CUMPRINC(rate,nper,pv,start_period,end_period,type)FV(rate,nper,pmt,pv,type)PV(rate,nper,pmt,fv,type)
all the time but here in CANADA we have semi annual compounding for mortgages
when the interest rate is fixed for the temr of the mortgage and the NPER for
the PMT is monthly (ie 300) but the compounding period is not monthly it is
semi annual.
 
I'm trying to build a spreadsheet that will calculate the PMT, FV CUMINT,
CUMPRINC etc etc for a range of interest rates, PV's and NPER's. I can do for
Variable Rates which are compounded monthly (for obvious reasons) but with
Fixed Rates (here in Canada) the quoted annual rate must be compounded semi
annually (and not monthly as in the US). I cannot determine how to do this
and thought there may be a quick solution. I was not aware of the two
functions you pointed out and will have a close look at them. Thanks.
 
Maybe you can check your results with the Canadian Mortgage calculator here:

http://www.canadamortgage.com/calculators/amortization.cgi

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| I'm trying to build a spreadsheet that will calculate the PMT, FV CUMINT,
| CUMPRINC etc etc for a range of interest rates, PV's and NPER's. I can do for
| Variable Rates which are compounded monthly (for obvious reasons) but with
| Fixed Rates (here in Canada) the quoted annual rate must be compounded semi
| annually (and not monthly as in the US). I cannot determine how to do this
| and thought there may be a quick solution. I was not aware of the two
| functions you pointed out and will have a close look at them. Thanks.
|
| "Niek Otten" wrote:
|
| > You'll have to establish an effective rate per month, taking into account semi-annual payments.
| > There have been many discussions here about the way one should do that. If your aim is to check or predict the calculations of
| > your mortgage company you'll have to get their calculation rules.
| > In the meantime you could experiment with the EFFECT() and NOMINAL() functions.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Excel provides functions to calculate a variety of numbers for mortgages
| > | (when they have a monthly compounding interest rate) but I cannot find
| > | functions to generate those numbers when the quoted annual rate is compounded
| > | on a semi annual basis.
| >
| >
| >
 
I'm sorry I read your question as less complicated than you intended.

Nominal and Effect are part of the Analysis Toolpak Add-In for versions
before '07.
Click Tools>AddIns to make them available.

However, will this one work for you?

=PMT((7.5%/2+1)^(2/12)-1,360,100000)

=Pmt((rate/2+1)^(2/12)-1,nper, pv)
 
I tried it but the equation does not generate the known payment.
PMT=$1,609.17, Nper=360, PV=$285,000 and Rate=5.25%
 
BoniM, I have used your information and tht from another source and come up
with the following (very complex) equation that generates the correct PMT!
Here is is ---
=(((((1+%Rate/2)*(1+%Rate/2)-1)+1)^(1/12)-1)*PV)/(1-1/(1+((((1+%Rate/2)*(1+%Rate/2)-1)+1)^(1/12)-1))^(Nper)) --- Thanks for the help ... Iain :O)
 
Ahhh... back to the basics! Math without functions! Tho I thought I was
fixing the function to do the same. :-( It should have given you the same
answer as below, but I guess I'll have to play and figure out where I went
wrong. Having any luck modifying the other functions? or is this the only
one you really needed?
 
I'm rusty with the math and the equation is hard to use but I feel like I'm
making progress. I wish I could show you what I have developed (the xls) as
you may be able to stear me in more expedient direction. Are we permitted to
exchange email ids or is that verbotten? I'm the President of the PMI
Canadian West Coast Chapter at pmi.bc.ca :O)
 
Back
Top