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)

"BoniM" wrote:

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?

"Iain" wrote:

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)

"BoniM" wrote:

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 ToolsAddIns 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)




"Iain" wrote:

BoniM, thanks for your reply. I use these functions
(PMT(rate,nper,pv,fv,type)CUMIPMT(rate,nper,pv,sta rt_period,end_period,type)CUMPRINC(rate,nper,pv,st art_period,end_period,type)FV(rate,nper,pmt,pv,typ e)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.

"BoniM" wrote:

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.