Loan repayment calculator

%3CLINGO-SUB%20id%3D%22lingo-sub-2222670%22%20slang%3D%22en-US%22%3ELoan%20repayment%20calculator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2222670%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20simple%20loan%20repayment%20calculator.%3C%2FP%3E%3CP%3EBasically%2C%20I%20want%20to%20make%20a%20worksheet%20to%20insert%20date%2C%20payment%20amount%2C%20interest%20and%20principle%20totals%2C%20balance.%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2222670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2222774%22%20slang%3D%22en-US%22%3ERe%3A%20Loan%20repayment%20calculator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2222774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1002637%22%20target%3D%22_blank%22%3E%40seasupport%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20template%20for%20Excel%20that%20appears%20to%20do%20what%20you're%20seeking.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftemplates.office.com%2Fen-us%2Floan-amortization-schedule-tm03986974%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftemplates.office.com%2Fen-us%2Floan-amortization-schedule-tm03986974%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20use%20it%20to%20learn%20how%20to%20create%20your%20own.%20I'm%20sure%20you%20could%20also%20use%20Google%20or%20some%20other%20search%20engine%20for%20pointers.%20The%20finance%20functions%20in%20Excel%20require%20a%20bit%20of%20a%20learning%20curve%2C%20but%20are%20worth%20mastering.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2225996%22%20slang%3D%22en-US%22%3ERe%3A%20Loan%20repayment%20calculator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20what%20I%20am%20looking%20for.%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20some%20type%20of%20worksheet%20that%20you%20can%20input%20the%20date%2C%20amount%20of%20payment%20that%20would%20calculate%20interest%20and%20principle%20and%20reduce%20the%20amount%20owed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2226545%22%20slang%3D%22en-US%22%3ERe%3A%20Loan%20repayment%20calculator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1003951%22%20target%3D%22_blank%22%3E%40seasupport1915%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20You%20wrote%3A%3C%2FP%3E%3CP%3E%60%60%20I%20want%20to%20make%20a%20worksheet%20to%20insert%20date%2C%20payment%20amount%2C%20interest%20and%20principle%20totals%2C%20balance%60%60%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3E%60%60I%20am%20looking%20for%20some%20type%20of%20worksheet%20that%20you%20can%20input%20the%20date%2C%20amount%20of%20payment%20that%20would%20calculate%20interest%20and%20principle%20and%20reduce%20the%20amount%20owed%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20still%20unclear%20(to%20me)%20what%20you%20want%20to%20input%2C%20and%20what%20you%20want%20to%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20best%20for%20you%20to%20mock%20up%20a%20worksheet%20with%20the%20input%20data%20and%20the%20results%20laid%20out%20the%20way%20you%20want.%26nbsp%3B%20Enter%20example%20input%20data%3B%20and%20where%20possible%2C%20enter%20example%20results%20that%20you%20calculate%20or%20estimate%20manually.%26nbsp%3B%20Perhaps%20you%20can%20find%20an%20online%20calculator%20to%20determine%20the%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttach%20the%20Excel%20file%20to%20your%20response%20that%20expands%20on%20your%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20say%20that%20want%20to%20%22reduce%20the%20amount%20owed%22.%26nbsp%3B%20To%20that%20end%2C%20we%20would%20need%20to%20know%20the%20following.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Current%20loan%20terms.%26nbsp%3B%20That%20would%20be%20current%20remaining%20balance%2C%20current%20payment%2C%20payment%20frequency%2C%20remaining%20number%20of%20payments%2C%20current%20interest%20rate%2C%20and%20any%20final%20balance%20(if%20not%20zero).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20express%20a%20periodic%20interest%20rate%20for%20the%20payment%20frequency%20(e.g.%20monthly).%26nbsp%3B%20If%20it%20is%20an%20annual%20rate%2C%20you%20need%20to%20tell%20us%20how%20to%20convert%20to%20a%20periodic%20rate.%26nbsp%3B%20There%20are%20several%20common%20methods%3A%26nbsp%3B%20(a)%20divide%20the%20annual%20rate%20by%20the%20payment%20frequency%3B%20(b)%20the%20annual%20rate%20is%20the%20periodic%20rate%20compounded%20by%20the%20payment%20frequency%3B%20and%20(c)%20the%20Canadian%20method%20(sigh)%2C%20which%20applies%20only%20to%20mortgages%2C%20IIRC.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20that%20assumes%20a%20fixed-rate%20loan.%26nbsp%3B%20If%20it%20is%20a%20variable-rate%20loan%2C%20we%20need%20to%20know%20the%20terms%20of%20the%20variable%20rate%20plan.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Terms%20of%20the%20repayment%20loan%20that%20are%20known.%26nbsp%3B%20You%20say%20that%20you%20want%20to%20%22reduce%20the%20amount%20owed%22.%26nbsp%3B%20I%20think%20you%20mean%3A%20reduce%20the%20total%20amount%20__paid__%20over%20the%20remaining%20term%20of%20the%20loan.%26nbsp%3B%20We%20cannot%20reduce%20the%20amount%20of%20principal.%26nbsp%3B%20We%20__can__%20reduce%20the%20amount%20of%20interest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20without%20some%20boundaries%2C%20there%20is%20an%20infinite%20number%20of%20ways%20to%20reduce%20the%20total%20payment.%26nbsp%3B%20For%20example%2C%20the%20smallest%20total%20payment%20is%20principal-only%20in%20a%20single%20payment.%26nbsp%3B%20I'm%20sure%20that%20is%20not%20what%20you%20have%20in%20mind.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20variables%20that%20might%20reduce%20the%20total%20payment%20(i.e.%20total%20interest)%20are%3A%26nbsp%3B%20(a)%20the%20remaining%20term%20of%20the%20loan%20(longer%20or%20shorter)%3B%20(b)%20the%20interest%20rate(s)%20--%20and%20fixed%20v.%20variable%20rate%3B%20(c)%20payment%20frequency%3B%20(d)%20final%20balance%20due%20after%20all%20regular%20payments.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Also%2C%20a%20specific%20periodic%20payment%2C%20if%20you%20have%20one%20in%20mind%20or%20a%20range.%26nbsp%3B%20But%20it%20sounds%20like%20that%20is%20what%20you%20want%20to%20calculate.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20are%20you%20looking%20for%20a%20design%20that%20would%20allow%20you%20to%20manually%20make%20what-if%20changes%20to%20see%20the%20effect%20of%20step-wise%20changes%20to%20the%20current%20loan%20terms%20on%20the%20total%20payment%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrankly%2C%20that%20could%20be%20a%20%22heavy%20lift%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%20you%20refine%20your%20question%20so%20that%20you%20can%20get%20some%20helpful%20responses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Is there a simple loan repayment calculator.

Basically, I want to make a worksheet to insert date, payment amount, interest and principle totals, balance.

  

3 Replies

@seasupport 

 

Here's a template for Excel that appears to do what you're seeking.

https://templates.office.com/en-us/loan-amortization-schedule-tm03986974

 

You might use it to learn how to create your own. I'm sure you could also use Google or some other search engine for pointers. The finance functions in Excel require a bit of a learning curve, but are worth mastering.

@mathetes 

Not what I am looking for.

I am looking for some type of worksheet that you can input the date, amount of payment that would calculate interest and principle and reduce the amount owed. 

@seasupport1915   You wrote:

`` I want to make a worksheet to insert date, payment amount, interest and principle totals, balance``

and

``I am looking for some type of worksheet that you can input the date, amount of payment that would calculate interest and principle and reduce the amount owed``

 

It is still unclear (to me) what you want to input, and what you want to change.

 

It would be best for you to mock up a worksheet with the input data and the results laid out the way you want.  Enter example input data; and where possible, enter example results that you calculate or estimate manually.  Perhaps you can find an online calculator to determine the results.

 

Attach the Excel file to your response that expands on your explanation.

 

You say that want to "reduce the amount owed".  To that end, we would need to know the following.

 

1. Current loan terms.  That would be current remaining balance, current payment, payment frequency, remaining number of payments, current interest rate, and any final balance (if not zero).

 

Ideally, express a periodic interest rate for the payment frequency (e.g. monthly).  If it is an annual rate, you need to tell us how to convert to a periodic rate.  There are several common methods:  (a) divide the annual rate by the payment frequency; (b) the annual rate is the periodic rate compounded by the payment frequency; and (c) the Canadian method (sigh), which applies only to mortgages, IIRC.

 

Of course, that assumes a fixed-rate loan.  If it is a variable-rate loan, we need to know the terms of the variable rate plan.

 

2. Terms of the repayment loan that are known.  You say that you want to "reduce the amount owed".  I think you mean: reduce the total amount __paid__ over the remaining term of the loan.  We cannot reduce the amount of principal.  We __can__ reduce the amount of interest.

 

However, without some boundaries, there is an infinite number of ways to reduce the total payment.  For example, the smallest total payment is principal-only in a single payment.  I'm sure that is not what you have in mind.

 

The variables that might reduce the total payment (i.e. total interest) are:  (a) the remaining term of the loan (longer or shorter); (b) the interest rate(s) -- and fixed v. variable rate; (c) payment frequency; (d) final balance due after all regular payments.

 

(Also, a specific periodic payment, if you have one in mind or a range.  But it sounds like that is what you want to calculate.)

 

Or are you looking for a design that would allow you to manually make what-if changes to see the effect of step-wise changes to the current loan terms on the total payment?

 

Frankly, that could be a "heavy lift".

 

Hope that helps you refine your question so that you can get some helpful responses.