Loan Calculation Formula

[danbl]

I have the standard loan calculation spreadsheet but am looking for a variation which will show the payment as the remining balance decreases.

I have looked at the payment function but have not come up with a solution yet. Can someone help??

Thank you

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

What do YOU mean by the standard loan calculation spreadsheet? Are you looking for a single function or a table of values?

[danbl]

I have attached to both

This was original downloaded from another site.

[Richard Buttrey]

I'm not sure what you're expecting.

Given the three inputs of original loan, a rate of interest which varies with the amount outstanding , and the number of periods, the payments are what they are. Specifically from the PMT() function

=-PMT(InterestRate/12,NumberOfPayments,LoanAmount)

A change to the payment in a period will result in the term of the loan changing unless the interest rate is also changed to compensate in order to keep to the original term.

Perhaps you could clarify what you are asking for.

[danbl]

The current formula correctly calculates payment which stays the same.

What I am attempting to do is, as the balance decreases each period, the payment amount also decreases for the next payment.

The attached calculator uses "named formulas" which I have minimal experience with editing to possibly come up with a solution. I may be making this more complicated than it needs to be??

[Gregb11]

What I am attempting to do is, as the balance decreases each period, the payment amount also decreases for the next payment.

Why? That's not the way loans work. The payment should remain constant - the interest charge reduces and the principal increases as the balance decreases.

[Richard Buttrey]

The current formula correctly calculates payment which stays the same.

What I am attempting to do is, as the balance decreases each period, the payment amount also decreases for the next payment.

The attached calculator uses "named formulas" which I have minimal experience with editing to possibly come up with a solution. I may be making this more complicated than it needs to be??

Forget the named formulae they are irrelevant here. Using relative cell references would produce the same result.

Although you haven't specifically said what result you expect, the workbook you've posted gives a result
It has variables for Loan amount, interest rate %, a loan term and the amount of principal paid to obtain the 'result'. Your example has chosen to FIX the number of periods and the AMOUNT paid each period so that Interest and Principal paid each month varies


At the moment the unique combination of 4.4% interest and 15 years of payments results in a $60.79 payment per month. Any change to the payment per month necessarily means that the Principal paid off each month needs to change if the same term of 15 years is kept.

Maybe the attached is what you want.