Loan Amortization template

[Brian]

I believe I have uncovered an error on this template when trying to change
the number of payments per year and have it calculate the Actual Number of
Payments required. If for example I enter
Loan Amount: $180000
Int rate: 6.25
Loan Period yrs: 30
Num of Payments: 12
Start Date: 8/1/2007
....everything computes just fine (scheduled # payments & actual # payments =
360). If however I wish to understand how to see how bimonthly payments can
help my overall payment schedule and total interest I would think all I need
to do was enter: Number of Payments per yr: 24. Unfortunately when I do,
the scheduled # payments is correct at 720, however the Actual number of
payments can never go higher than 361 which is absolutely incorrect. If you
wish to verify, just go the bottom of the schedule and see how much balance
you have left!
Can anyone help???

 

[Bernie Deitrick]

Brian,

A1 12
A2 30
A3 180000
=PMT(0.0625/A1,A1*A2,A3)

With the above formula, you can change A1 from 12 to 24 to see your savings. Which won't be much -
$0.49 per month.

The real change in interest costs is when going to bi-weekly payments from monthly, BUT you make
half of the monthly payment every two weeks, not what PMT would return. Basically, you end up
making 26 halves of your monthly payments per year, for a total of 13 months of payments instead of
12. Then the power of compounding kicks in....

HTH,
Bernie
MS Excel MVP

 

[Brian]

Hello Bernie,

Thank you for the quick response. I still see a fundamental problem with
this template (using Office XP-2002). I do see how the amount of total
interest is correct but not the total number of expected payments. If I take
this example (and the template) and try to determine how many total number of
payments I will have to make to have Zero balance left.
A1 12 (number of payments per year)
A2 30
A3 180000
=PMT(0.0625/A1,A1*A2,A3)
"Scheduled Number of Payments": 360 ...ok
"Actual Number of Payments": 360 ...ok
"Ending Balance": $0.00

Ex 1: change A1 = 13 (ie 13 payments per year)
"Scheduled Number of Payments": 390 (13 * 30) ...ok
"Actual Number of Payments": 361 ??? ...WRONG
"Ending Balance": $28,514.90

Ex 2: change A1 = 24 (ie 24 or 26 payments per year)
"Scheduled Number of Payments": 720 (24 * 30) ...ok
"Actual Number of Payments": 361 ??? ...WRONG
"Ending Balance": $129,302.62

Ex 3: change A2 = 25 yrs & keep A1 = 24 (ie 24/26 payments per year)
"Scheduled Number of Payments": 600 (24 * 25) ...ok
"Actual Number of Payments": 361 ??? ...WRONG
"Ending Balance": $105,795.71

Ex 4: change A1 = 15 (ie 24/26 payments per year) & keep A2 = 25 yrs
"Scheduled Number of Payments": 375 (15 * 25) ...ok
"Actual Number of Payments": 361 ??? ...WRONG
"Ending Balance": $13,781.93

I still have not found which formula is limiting the number of "Actual
Number of Payments" to 361. I apologize for being so wordy with my examples.
Thanks, Brian

 

[Bernie Deitrick]

Brian,

The problem is that the template is set up for a maximum of 360 payments. To fix it, select rows
A376:A735, and use Insert / Rows. Then select the cells A374:J375, grab the fill handle, and drag
down for 361 rows, to row 737.

HTH,
Bernie
MS Excel MVP

 

[Brian]

ok, ok, ok...silly me. I had tried to do just that when I first started
playing with it. But sometime later I went and unprotected the sheet and did
not go back and try to fill in the formulas I believe. I also see what you
mean now about the PMT not showing exactly what I was expecting since I will
actually be making 26 pymts/yr. A financial advisor had mentioned that by
asking for biweekly mortgage payments you could reduce a typ 30 yr loan into
~23yrs. I had been making the assumption that it would still be 24pymts/yr
and there was some form of interest compounding at work, and not simply from
the additional 2 yrly pymts. Thanks for your help!!!

Brian

 

[Bernie Deitrick]

Brian,

If you simply make one extra (equal) payment at the end of each year against the principal (for a
conventional 30 year, 12 pmts per year mortgage), you cut the loan by 5 1/2 years. Make the payment
at the beginning of the year, and you cut the loan by almost 6 years.

HTH,
Bernie
MS Excel MVP

 

[challa prabhu]

Hi,

Give "Loan Template" as Serach topic in the Excel on-line help. You will get
a list of all available templates loan and amotization templates.

Challa Prabhu

 

[Stan Brown]

Fri, 13 Jul 2007 09:26:07 -0700 from Brian

If however I wish to understand how to see how bimonthly payments can
help my overall payment schedule and total interest I would think all I need
to do was enter: Number of Payments per yr: 24.

Bimonthly payments would amortize your loan slower, since there would
be six of them a year, not 24.