Amortization schedule problem

[albertamortgageguy]

I was wondering if someone can help me. I've just started at a new bank and they have an unusual way to pay mortgages, which is completely different to the usual way that each payment is split into principal and interest. The total mortgage payment goes to the principal and the interest is charged per day and at the end of six months the interest is added back on top of the principle. I haven't been able to find an amortization spreadsheet which will calculate this and I would like to be able to have a side by side comparison with differing payment frequencies and also a chart
thanks for the help
jon

 

[Paella1]

As far as I can tell this is no different to any other loan, with the exception that interest is compouned six-monthly rather than monthly. So you can schedule the payments where i = half the annual interest rate & n = 2 x the term in years. This will give the required repayment that is due in every six-month period. The required monthly repayment is simply this number divided by six.

Keep in mind the commonly observed method of splitting repayments between principal and interest is of no relevance to the calculation, nor the financial reality. All that matters is that interest is added to the loan balance and repayments are deducted from the loan balance. It doesn't matter how many components you divide the repayment into, nor does it matter what you call them.

 

[Dave Patton]

1. Some or many Canadian mortgages have monthly payments but the interest compounds half-yearly. The same concept can apply with bi-monthly payments etc.

You can adjust the interest factor and use PMT; Excel still doesn't have a Pmtc.

Example with 5% =(1+.05/2)^(2/12)-1 calculated rate

=PMT((1+Rate/2)^(2/12)-1,Term*12,Principal,0,0)

2. You could create your own User-Defined Function UDF to make the formula more user friendly.

With the above, you can calculate the monthly payment acccording to many mortgage documents.

You can set up a monthly amortization with the above or prepare your schedule using Excel's arithmetic functions.

hth Dave

 

[albertamortgageguy]

Thanks for the help :>