Posted on 08/14/2018 3:58:09 PM PDT by MileHi
Is anyone aware of an Xcel form for amortization that will calculate scheduled payments and adjust for actual payments? Especially over a 3 year term with balloon.
Thanks if you can
This site might have it:
Don’t let the site name throw you - lots of financial calculators there.
Never understood the fascination with balloons. Keep the balloons and just give me the money.
Okay, maybe a bottle of scotch and the money.
google ain’t my friend, I hate ‘em. But thanks or that.
2016 Excel has mortgage templates. Just pulled up two of them by searching for “mortgage” in the Template section. The second template I tried had an amortization schedule attached.
I have 2010, any idea where those templates are?
Not hard to build one, but a little harder to understand how the calculation actually works. Would suggest looking up instructions on using the pmt function.
=pmt(rate, numberPayments, loanAmount, balloon)
Sign of loanAmount and pmt are opposite. Negative numbers appear in red.
or start a table for irregular payments:
In A1 enter text “Date”
In B1 enter text “balance”
in C1 enter text “payment”
in A2 enter the origination date
in B2 enter the loan amount
in C2 enter 0.
In A3 enter the date of the subsequent payment.
In B3 enter =B2*(1 + ratePercent/1200) - C3
where ratePercent in the interest rate in percent, 1200 converts from annual interest rate in percent to monthly period, as a decimal fraction. For instance, 6% annual rate, monthly is 6/1200 = 0.005 For different rates and periods, adjust as required.
In C3 enter the actual payment amount.
Repeat the entries in row 3 until the balance vanishes. Try it for the constant interest, equal payment case, the table should closely match (within a penny) the pmt calculation result.
Good luck!
Click on “File” in the upper left corner and when the menu comes up, click on “New”. It will bring up all kinds of templates for you.
Thanks, I think.
I don’t know, but would think they’re available in there somewhere. You might have to download from the MS site. Can you search in Help? Search for “template”?
I had 2010 on my last CPU, but it died so can’t help there.(darnit!).
Thanks, I found that.
I found a template that seems to work. You plug in the info and it calc, except when I arrow down it only moves the last line at the bottom, the table for the info stays put. Know how to ix that?
Try hitting enter or tab.
I still have an old Hewlett-Packard 12C that I rely on for doing amortizations. No print function though. No wifi, blue tooth, etc. Just keeps working as long as I replace the 3 little batteries.
OK Dinners up, be right back...
try any mortgage website. they usually have a calculator.
I like you.
I’m a DYI kinda guy, especially with Excel.
Disclaimer: Opinions posted on Free Republic are those of the individual posters and do not necessarily represent the opinion of Free Republic or its management. All materials posted herein are protected by copyright law and the exemption for fair use of copyrighted works.