The template is for version excel 2007.
I actually needed the URL ("
http://...") or, if it is an MS Office
Online template, the name of the template that you selected.
You type in the loan amount, interest rate, loan period, no. of
payments, start date of loan and any extra payments and it
calculates your scheduled payment, the number, and the total
interest amount.
The devil is in the details. Most templates compute the payment so
that it reduces the loan to zero in the stated loan period at the
stated interest rate. In order to allow for a balloon payment, a
properly-designed template would allow you specify that amount, too.
Looking at the MS Office Online template "Mortgage amortization
schedule" (by TemplateZone by KMT Software), I do not see that
feature.
(I am using that template as an example. I don't think you are using
it because you mention a feature that it does not have.)
In theory, it could be added fairly easily. Insert a line under the
end of the "Inputs" table for the balloon payment, if any, and change
the formula for "Monthly payments" by adding the following for the
"fv" argument: IF(ISNUMBER(E9),E9,0).
(That is probably applicable to the template that you are using, too,
changing E9, of course.)
But ironically, that also demonstrates how non-straight-forward such a
change can be -- that is, "the devil is in the details".
As it happens, that change alone does not work. For that template,
the problem is that the annual "Ending Balance" is computed with a non-
intuitive formula (IMHO): PV() of the ending loan balance (presumed
to be zero) instead of FV() of the previous ending balance.
I could tell you how to fix the template that I mentioned. But there
is little point to that, since it probably would not be applicable to
the template you are using (hopefully <g>).
Anyway, this is probably not directly useful. But I hope it gives you
some appreciation for the care with which you need to proceed with any
suggestions.
