Skip to content

Archives

Excel mortgage payment calculator formula


excel mortgage payment calculator formula

Use this excel based mortgage payment calculator to calculate your estimated monthly mortgage payments for various loan amounts and interest. The Vertex42® Mortgage Payment Calculator is a very simple spreadsheet that lets you compare different mortgages side-by-side. How to Calculate Your Monthly Mortgage Payment. You can calculate your monthly mortgage payments using the following formula: M = P [ I (1 + I).

Excel mortgage payment calculator formula -

Microsoft Excel can be used to create a mortgage calculator. The mortgage calculator can be used to calculate the monthly payment on a mortgage based on different variables for loan amount, interest rate and the term of the loan. Here are the steps to create a mortgage calculator in Excel:

Step 1: Create Headings

Create headings in different cells for loan amount, interest rate, loan term, payments per term and payment amount. Headings should be placed in column 1 in the order listed.

Step 2: Format Cells

The cells containing values go in column 2. The cells containing loan amount and payment amount need to be for currency. The interest rate should be a percentage. Loan term and payments per term are integers.

Step 3: Give Cells a Name

The first cell in Excel is A1. You can replace this name with something else. For example, you could replace the names of your cells with the following names: Loan_Amount, Interest_Rate, Loan_Years, and Num_Pmt_Per_Year.

Step 4: Create Formula

The payment amount cell will contain the formula. The formula will use a built-in function called PMT. This function uses input from the other fields to calculate the monthly mortgage payment. Using the cell names above, enter the following formula in the payment amount cell:

=-PMT( Interest_Rate/Num_Pmt_Per_Year,Loan_Years * Num_Pmt_Per_Year,Loan_Amount)

Источник: https://www.finweb.com/real-estate/real-estate-tips-building-an-excel-mortgage-calculator.html

Learning Outcomes

  • Use financial functions and formulas

Excel provides specific formulas and functions to assist with financial calculations. We will cover the top five most often used financial functions. The following scenario will inform the next example of financial functions.

A person secured a loan from a bank to purchase an apartment at an annual interest rate of 6%, over 20 years, with monthly payments. The loan has a present value (PV) of $100,000 (amount of new loan) and a future value (Fv) of 0 (because the goal of the loan is to have it completely paid off at the end of the time period). For the monthly payments use 6%/12 = 0.5% for Rate, and 20*12 = 240 for Nper (total number of payment periods). If only annual payments were made, then it we would use 6% for the Rate and 20 for Nper.

Payment (PMT)

Payment terms for a loan or investment. The Excel formula for it is =PMT(rate,nper,pv,[fv],[type]). This assumes that payments are made on a consistent basis.

Follow these steps to find the monthly payment amount for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab, Financial button, scroll until you find PMT in the drop-down menu.
    Excel screenshot of dropdown menu for Financial formulas in Excel's Formulas tab. Highlighting the PMT option.
  3. A dialog window asking for the inputs opens. Click on the cells matching the correct information for that field and the data will be entered into the formula automatically. Using the cell location instead of typing in the straight data, means the formula will automatically update if new data is entered into those cells. For the last two areas (Fv,Type) for loans, Fv can be omitted (or enter 0) and Type can also be left empty. Type left empty assumes that payments come due at the end of the period.
    Excel screenshot of PMT formula function argument dialog box. Each of the field required to the formula are being filled in.
  4. After the calculation, the monthly loan payment is $716.43. The figure is red because it is a debt paid against the total loan. (If you wish to have it not show as a red/negative number, type in a minus sign before the B2 Rate (-B2) and the PMT will show as a black, positive number.) The second screenshot shows the PMT formula and the corresponding cells associated with the formula. To see the formula correspondence, double clicking on the cell. This correspondence association works with any formulas used.
    Excel screenshot of the PMT data and calculated amount.Excel screenshot the PMT data after double clicking on the formula cell. All of the corresponding cells associated within the formula have colors corresponding to their place in the formula.
  5. With the formula established, when you change the rate, present value or nper, you can see the change in monthly payment it will take to pay off the loan.

Number of Periods (NPER)

Number of periods per loan or investment. The Excel formula for this is =NPER(rate,pmt,pv,[fv],[type]).

Follow these steps to find the number of periods for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab, Financial button, scroll until you find NPER in the drop-down menu.
  3. A dialog window asking for the inputs opens. Enter the corresponding cell locations.
    Excel screenshot of dropdown menu for Financial formulas in Excel's Formulas tab. Highlighting the NPER option.Excel screenshot of NPER formula function argument dialog box. Each of the field required to the formula are being filled in.
  4. The Nper is 240 months (20 years of monthly payments). The second screenshot shows the NPER formula and the corresponding cells associated with the formula. Double click on the cell to see its corresponding cells.
  5. With the formula established, changing the monthly payment amount shows the change in the number of periods needed to pay off the loan.
Excel screenshot of the NPER data and calculated amount.Excel screenshot of NPER data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

Rate (RATE)

The interest rate for a loan or a rate of return required to attain a specific amount on an investment over a period of time. The Excel formula for this is =RATE(nper,pmt,pv,[fv],[type],[guess]). The “guess” for our purposes can be left out for this scenario.

Follow these steps to find the interest rate for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab > Financial button > Scroll until you find RATE in the drop-down menu.
  3. Dialog window opens > Enter the corresponding cell locations.
  4. The Rate is 0.50%. The second screenshot shows the Rate formula and the corresponding cells associated with the formula. Double click on the cell to see its corresponding cells.
Excel screenshot of the RATE data and calculated amount.Excel screenshot of RATE data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

Present Value (PV)

Present value of a loan or investment based on a constant interest rate (like a mortgage or loan). The Excel formula for this is =PV(rate,nper,pmt,[fv],[type]).

Let’s change the scenario a little to find a PV for a different loan. If you know that the monthly payments are $952.69, the interest rate is 6%, and the life of the loan is 20 years, what is the present value of that loan?

Follow the same steps to insert the formulas like before to find the present value for this loan.

Excel screenshot of the PV data and calculated amount.Excel screenshot of PV data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

After filling in the values for the above scenario the PV for this loan is $120,999.66 or if rounded up $121,000.

Future Value (FV)

Future value of an investment assuming constant, periodic payments with a constant interest rate. The Excel formula for this is =FV(rate,nper,pmt,[pv],[type]).

Let’s change the scenario again and find FV for a different loan’s time frame. If you know that the monthly payments are $895.54, the interest rate is 5%, and the life of the loan is 20 years, is the future value of that loan payed down to 0 in that 20-year time frame?

Follow the same steps to insert the formulas like before only using the FV function to figure this out.

Excel screenshot of the FV data and calculated amount.Excel screenshot of FV data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

The answer is that, yes, the future value of the loan comes to zero in the timeframe.

Practice Question

Now you know how to use these five financial functions and formulas in Excel.

Contribute!

Did you have an idea for improving this content? We’d love your input.

Improve this pageLearn More

Источник: https://courses.lumenlearning.com/wm-computerapplicationsmgrs-2/chapter/financial-functions-and-formulas/

Are you planning to take a loan? If so, what is your monthly payment would be? Will you be able to afford that payment? What is the best repayment period for you? You can easily find answers to these questions by performing a few easy calculations on spreadsheets. In this post, I will show you how you can Calculate Loan Repayments with Google Sheets.

For these calculations, you need to know the bank interest rate on advances of your bank for your intended loan type.

Calculating Monthly Loan Repayment Amount

You can calculate the monthly payment amount directly from the Google Sheet function .

: The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.

Let;

  • Annual Interest Rate is 6%
  • Your loan amount (PV) is $40,000
  • The repayment period is 7 years

You can apply the above values to the function as bellow, then you will get the monthly payment amount.

Finding the best combination

Depend on your repayment capacity, your requirement, and the interest rate you need to find the best combination of interest rate, repayment period, and loan amount. You can perform a what-if analysis using the above function as demonstrated in the following image to find the best combination. You can use the conditional formatting option to highlight values based on your limitations.

Calculating the amount of principal and interest for each month

In the above section, we calculated the total payment you need to pay to the bank each month. However, your principal and interest are not the same each month. You can calculate these principal and interest payments using using and equation.

: The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate.

: The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate.

With the function you can calculate the principal amount for a specific month and the relevant interest component is given by the function. The sum of the and components is the total amount that you have to pay each month. By subtracting the first-month principal from the loan amount you can get the balance principal amount after the first month. Subtracting the second-month principal amount from the first month’s balance you can get the balance principal amount after the second month. Likewise, you can calculate the balance principal amount after each month.

In the following example, I am calculating the monthly principal amount, monthly interest rate, monthly installment, and balance principal amount for a loan amounting to $40,000 with a 6% annual interest rate and 7 years repayment period.

By combining the above calculations, you can find the optimum loan amount and the best interest rate and repayment period to match your requirements. You can save your workings in your Google Drive and use it whenever you required to calculate loan repayments.

You can make a copy of the Google Sheets with the above workings to your drive from this link

Related

Categories SpreadsheetsTags Google SheetsИсточник: https://www.bpwebs.com/how-to-calculate-loan-repayments-with-google-sheets/

I am a professional computer analyst for a large accountancy firm. In my spare time, I write about video games and various other topics.

The PMT function within Excel is a financial function that is used to calculate loan payments (it can also be used to calculate the value of investments over time). With the associated IPMT and PPMT functions, it is easy to create a worksheet that will calculate an amortization schedule for a mortgage or other loan.

These functions give you the ability to model different loan situations, including additional principal payments, different loan amounts, different interest rates, etc., and calculate monthly total payments as well as payments over the life of the mortgage.

This article will provide a step-by-step guide on how to create a mortgage loan calculator using Microsoft Excel. While the worksheet has been designed in Microsoft Excel 2007, the functionality will be the same on Microsoft Excel 2003.

Creating the Format for the Mortgage Calculator

Setting up the initial format of the Mortgage Calculator
Setting up the initial format of the Mortgage Calculator
Adding the formula =-PMT(C3/12,C4,C2) to calculate the Total Monthly Payment

Setting up the initial format of the Mortgage Calculator

Explanation of Financial Functions Used in Mortgage Calculator

PMT, IPMT, & PPMT Functions - for a loan using a constant interest rate and payment:

  • PMT calculates the total payment.
  • IPMT calculates the interest payment for a given period.
  • PPMT calculates the principal payment for a given period.

Syntax of these functions:

  • =PMT(rate, number of payments, present value, [Future Value], [type])
  • =IPMT(rate, period, number of payments, present value, [Future Value], [type])
  • =PPMT(rate, period, number of payments, present value, [Future Value], [type])

Where:

  • Rate: the monthly interest rate for the loan.
  • Period: the period for which you want to calculate the interest or principal.
  • Number of payments: the total number of payments of the loan.
  • Present value: the current value that the future payments are worth now.
  • Future Value: the future value you want to attain after last payment. Leaving this out will assume the last value is zero.
  • Type: indicates when the payments are due. (0 is end of period, 1 is beginning of period.)

The process to create a mortgage schedule in Microsoft Excel is relatively simple, although it does use some complex financial functions.

Start off by creating a simple worksheet where the data will be entered and the amortization will be calculated. In this example, add the basic variables such as loan amount, number of months, interest rate, etc., and the headings for the actual amortization schedule. To do this:

  • Create a range with the main loan details: Total Loan, Interest Rate, Months. These are the variables that will allow for modeling of different loan terms.
  • Add Total Monthly Payment, Total Payments and Total Interest Paid to this range – these will be calculated later.
  • Create the actual schedule by adding heading: Month, Opening Balance, Principal, Interest, Extra Payment, Closing Balance and Total Payment. Once formulas are added, this section will automatically calculate the amortization schedule based on the variables input in the main loan range.
  • Add a loan amount, and interest rate and the number of months to the range. This is done to ensure that the formulas are working and can be any value.
  • Format the headings and range.
  • Add the formula =-PMT(C3/12,C4,C2) to cell C5 (See explanation of PMT on the right) IMPORTANT NOTE: the interest in the main range is an annual interest. Divide this value by 12 to get the monthly interest rate used in the formula)

The basic format is complete. Other items that could be added (see above screenshot for an example of a more complex final worksheet):

  • PMI payment
  • Monthly Insurance Payment
  • Property Taxes

Adding the Initial Formulas to the Mortgage Calculator

Now that the initial format is set, the remaining formulas in the summary range and the top row in the amortization schedule can be created. In each cell referenced below, enter the relevant formula.

CellFormulaComment

C6

=SUM(H10:H500)

Totals all the 'Total Payment' rows to give an overall payment made (principal and interest).

C7

=SUM(E10:E500)

Totals all the 'Interest' rows to give an overall interest payment made.

B10

1

As this is the first payment, this is set as 1.

C10

=+C2

As this is the first payment, this is set as the loan value.

D10

=-PPMT($C$3/12,B10,$C$4,$C$2)

Calculates the principal payment for this period only.

E10

=-IPMT($C$3/12,B10,$C$4,$C$2)

Calculates the interest payment for this period only.

F10

 

No value assigned yet.

G10

=+C10-D10-F10

Calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only.

H10

=+D10+E10+F10

Calculates the total monthly payment (principal plus interest).

Adding the Final Formulas to the Mortgage Calculator

The first row of formulas has been created. Now the rest of the formulas can be created. They essentially are the same but check to see if the month is blank (the month checks to see if the prior closing balance is zero and returns blank if it is, otherwise it returns the value of the prior month plus one. In each cell referenced below, enter the relevant formula.

CellFormulaComment

B11

=+IF(G10>1,IF(B10="","",B10+1),"")

This checks to see if there is a Closing Balance first; then it checks to see if the month prior has a value; if both values or true then it adds one to the prior month's value.

C11

=+IF(B11="","",G10)

If the value of the Month is not blank then this uses the value from the prior month Closing Balance, otherwise it leaves the cell blank.

D11

=IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2))

If the value of the Month is not blank then this calculates the principal for this period only, otherwise it places zero in the cell.

E11

=IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2))

If the value of the Month is not blank then this calculates the interest for this period only, otherwise it places zero in the cell.

F11

No value assigned yet

G11

=IF(B11="",0,+C11-D11-F11)

If the value of the Month is not blank then this calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only, otherwise it places zero in the cell.

H11

=IF(B11="",0,+D11+E11+F11)

If the value of the Month is not blank then this calculates the total monthly payment (principal plus interest, otherwise it places zero in the cell.

Copy these formulas down to row 500. The basic mortgage calculator is now complete. The amortization schedule will automatically recalculate when the values in cells C2, C3 and C4 are amended.

Adding Extra Payments to the Mortgage Calculator

Once the design is complete you can amend any of the loan details to model the amortization; additionally you can add Extra Payments throughout the entire loan. In the example an extra payment has been applied to the loan every month. This extra payment reduces the length of the loan, the total paid and the interest paid. This extra payment can be made at any time during the loan and doesn’t have to be monthly.

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Comments

Michelle Dee from Charlotte, NC on September 11, 2012:

Very useful and this looks like it took a lot of hard work to put this hub together - it looks great and thanks for sharing, voted up and across (except funny).

chrissieklinger from Pennsylvania on July 21, 2012:

Very helpful; I had no idea you could do that in Excel 2007. Better route to go than using online web calculators.

Источник: https://turbofuture.com

Georges Excel Mortgage Loan Calculator v3.1

Regular price $ 9.95 USD

Regular priceSale price $ 9.95 USD

Unit price/ per 
Sale Coming Soon

Excel mortgage calculator with extra payments and mortgage calculator with amortization schedule. Whether you are in the market for real estate and looking for a low rate mortgage loan, refinance an existing mortgage loan, or keep track of your existing loan, this mortgage calculator tool can be helpful in calculating what if scenarios of mortgage payments, interest cost, and time of pay off if mortgage prepayments are made.

Key Features:

  • Simple Home Mortgage Loan Calculator.
  • Mortgage Calculator with amortization schedule.
  • Mortgage Calculator spreadsheet allows you to add additional principal payments for early payoff.  The Excel mortgage calculator allows you can see impact of extra principal payments on total interest cost and how you can save money on interest. Excel mortgage calculator provides the calculations to see how to pay off your mortgage early.
  • The mortgage calculator include 30 year mortgage calculator, 15 year mortgage calculator, and 10 year mortgage calculator or any length up to a 30 year mortgage and compare them side by side to see difference in mortgage interest costs.
  • The Excel mortgage calculator compare different interest rates, loan amounts, and length of loan to find the best monthly payment within your budget and see what total interest would be over life of loan.
  • Microsoft Excel mortgage template calculates how to pay off mortgage early and save money and interest costs over life of loan.  Mortgage amortization spreadsheet simulates how to pay off your mortgage quickly and how to pay off your mortgage faster by making additional principal payments each month thus reducing time to pay off mortgage in less than 30 years.
  • Paying down debt is an important goal, whether you want to pay down student loan debt, pay down credit card debt, or pay down your mortgage loan. This Excel mortgage calculator will help you estimate and track your home loan payment history and amortization and see how much money you can save by making extra principal payments.
  • Simple to use Excel mortgage payoff calculator and mortgage prepayment calculator give you tools and charts to help you track your progress of becoming mortgage debt free.
  • Mortgage calculator charts visually display comparison scenarios helping you see which option saves you the most interest cost.
  • For home mortgages, home loans, or loans with monthly payments where interest is calculated and accrued on a monthly basis. Not for simple interest loans where interest is calculated and accrued on a daily basis. For mortgages originated in the United States.
  • Simple mortgage calculator with Excel mortgage calculator formula that automatically calculate mortgage payments and mortgage amortization table so you do not have to know the Excel mortgage calculator formula since the Excel mortgage template is already made for you.
  • In one of the Excel worksheets, Excel calculates and displays a mortgage amortization table based on irregular extra principle payments that you can add monthly in addition to the regular monthly mortgage payment and shows you the mortgage interest savings from making those irregular extra payments on the mortgage loan to pay off home early.
  • Another benefit of the Excel mortgage calculator is that it can function like an online mortgage calculator in the sense that you can access it from any location, if you store the Excel mortgage calculator template online with an online storage service like Microsoft OneDrive or Dropbox.  As long as you have the correct version of Microsoft Excel on your computer, you can access your Excel mortgage calculator template wherever you have interest access. If you choose to store the Excel mortgage calculator template online, remember to store it in a private password protected account with password protected folders so that only you have access to the Excel file.  You can even password protect the Excel file.
  • Whether searching for a free mortgage calculator or the best mortgage calculator, consider this easy mortgage calculator with additional payments and mortgage calculator with amortization table.  A free mortgage calculator may not have all the features you are looking for in a mortgage calculator.
  • In addition to the process of taking out a mortgage loan and searching for the best mortgage rates or the lowest mortgage rates, you also need a good mortgage calculator to calculate your monthly mortgage payment and total interest cost of the home loan to see if it meets your monthly budget and is an affordable mortgage loan.
  • Mortgage calculator is delivered by digital download.  After you purchase the mortgage calculator, you will receive a link to download the Excel home loan calculator template.
  • Only for fixed rate mortgages and not for adjustable rate mortgages (ARM) or  variable rate mortgages where the mortgage interest rate is periodically adjusted as indicated in the mortgage loan terms from the lender.

System requirements:

  • PC with Microsoft Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, or Excel for Office 365 (Excel for Microsoft 365).
    (standalone version of Microsoft Excel or part of Office 365 subscription / Microsoft 365 subscription)
  • Mac computer with Microsoft Excel 2016 for Mac, Excel 2019 for Mac, Excel 2021 for Mac, or Excel for Office 365 for Mac (Excel for Microsoft 365 for Mac).
    (standalone version of Microsoft Excel or part of Office 365 subscription / Microsoft 365 subscription) (not compatible with Excel 2011 for Mac) 
  • Excel mortgage amortization calculator is not compatible with Google Sheets (Google Spreadsheets)
  • The mortgage loan spreadsheet calculator is not compatible with Microsoft's free "Excel Online" app that is part of the free Office Online apps. These online apps are web browser based and have limited features compared to the full desktop version of Microsoft Excel.

License Terms:

By purchasing you agree with the License Terms / Terms of Sale.

Excel is a registered trademark of Microsoft Corporation.

Источник: https://www.buyexceltemplates.com/products/georges-excel-mortgage-loan-calculator-spreadsheet

Schedule Loan Repayments With Excel Formulas

Loan repayment is the act of paying back money previously borrowed from a lender, typically through a series of periodic payments that include principal plus interest. Did you know you can use the software program Excel to calculate your loan repayments?

This article is a step-by-step guide to setting up loan calculations.

Key Takeaways:

  • Use Excel to get a handle on your mortgage by determining your monthly payment, your interest rate, and your loan schedule.
  • You can take a more in-depth look at the breakdown of a loan with excel and create a repayment schedule that works for you.
  • There are calculations available for each step that you can tweak to meet your specific needs.
  • Breaking down and examining your loan step-by-step can make the repayment process feel less overwhelming and more manageable.

Understanding Your Mortgage

Using Excel, you can get a better understanding of your mortgage in three simple steps. The first step determines the monthly payment. The second step calculates the interest rate, and the third step determines the loan schedule.

You can build a table in Excel that will tell you the interest rate, the loan calculation for the duration of the loan, the decomposition of the loan, the amortization, and the monthly payment.

Calculate the Monthly Payment

First, here's how to calculate the monthly payment for a mortgage. Using the annual interest rate, the principal, and the duration, we can determine the amount to be repaid monthly.

The formula, as shown in the screenshot above, is written as follows:

=-PMT(rate;length;present_value;[future_value];[type])

The minus sign in front of PMT is necessary as the formula returns a negative number. The first three arguments are the rate of the loan, the length of the loan (number of periods), and the principal borrowed. The last two arguments are optional, the residual value defaults to zero; payable in advance (for one) or at the end (for zero) is also optional.

The Excel formula used to calculate the monthly payment of the loan is:

= PMT((1+B2)^(1/12)-1;B4*12;B3)=PMT((1+3,10%)^(1/12)-1;10*12;120000)

Explanation: For the rate, we use the monthly rate (period of rate), then we calculate the number of periods (120 for 10 years multiplied by 12 months) and, finally, we indicate the principal borrowed. Our monthly payment will be $1,161.88 over 10 years.

Calculate the Annual Interest Rate

We have seen how to set up the calculation of a monthly payment for a mortgage. But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan. For that reason, we would like to know the corresponding annual interest rate.

As shown in the screenshot above, we first calculate the period rate (monthly, in our case), and then the annual rate. The formula used will be RATE, as shown in the screenshot above. It is written as follows:

=RATE(Nper;pmt;present_value;[future_value];[type])

The first three arguments are the length of the loan (number of periods), the monthly payment to repay the loan, and the principal borrowed. The last three arguments are optional, and the residual value defaults to zero; the term argument for managing the maturity in advance (for one) or at the end (for zero) is also optional. Finally, the estimate argument is optional but can give an initial estimate of the rate.

The Excel formula used to calculate the lending rate is:

=RATE(12*B4;-B2;B3) = RATE(12*13;-960;120000)

Note: the corresponding data in the monthly payment must be given a negative sign. This is why there's a minus sign before the formula. The rate period is 0.294%.

We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294 %) ^ 12-1 to obtain the annual rate of our loan, which is 3.58%. In other words, to borrow $120,000 over 13 years to pay $960 monthly, we should negotiate a loan at an annual 3.58% maximum rate.

Using Excel is a great way of keeping track of what you owe and coming up with a schedule for repayment that minimizes any fees that you might end up owing.

Determining the Length of a Loan

We will now see how to determine the length of a loan when you know the annual rate, the principal borrowed, and the monthly payment that is to be repaid. In other words, how long will we need to repay a $120,000 mortgage with a rate of 3.10% and a monthly payment of $1,100?  

The formula we will use is NPER, as shown in the screenshot above, and it is written as follows:

=NPER(rate;pmt;present_value;[future_value];[type])

The first three arguments are the annual rate of the loan, the monthly payment needed to repay the loan, and the principal borrowed. The last two arguments are optional, the residual value defaults to zero. The term argument payable in advance (for one) or at the end (for zero) is also optional.

=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)

Minus Sign Before the Formula

The corresponding data in the monthly payment must be given a negative sign. This is why we have a minus sign before the formula. The reimbursement length is 127.97 periods (months in our case).

We will use the formula = B5 / 12 = 127.97 / 12 for the number of years to complete the loan repayment. In other words, to borrow $120,000, with an annual rate of 3.10% and to pay $1,100 monthly, we should repay maturities for 128 months or 10 years and eight months.

Decomposing the Loan

A loan payment is composed of principal and interest. The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods.

The table above shows the breakdown of a loan (a total period equal to 120) using the PPMT and IPMT formulas. The arguments of the two formulas are the same and are broken down as follows:

=-PPMT(rate;num_period;length;principal;[residual];[term])

The arguments are the same as for the PMT formula already seen, except for "num_period," which is added to show the period over which to break down the loan given the principal and interest. Here's an example:

=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3) = PPMT((1+3,10%)^(1/12)-1;1;10*12;120000)

The result is shown in the screenshot above "Loan Decomposition" over the period analyzed, which is "one;" that is, the first period or the first month. We pay $1,161.88 broken down into $856.20 principal and $305.68 interest.

Loan Computation in Excel

It is also possible to calculate the principal and interest repayment for several periods, such as the first 12 months or the first 15 months.

=-CUMPRINC(rate;length;principal;start_date;end_date;type)

We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT. But here, we need the "start_date" and "end_date" arguments also. The "start_date" indicates the beginning of the period to be analyzed, and the "end_date" indicates the end of the period to be analyzed.

Here's an example:

=-CUMPRINC((1+B2)^(1/12)-1;B4*12;B3;1;12;0)

The result is shown in the screenshot "Cumul 1st year," so the analyzed periods range from one to 12 of the first period (first month) to the twelfth (12th month). Over a year, we would pay $10,419.55 in principal and $ 3,522.99 in interest.

Amortization of the Loan

The prior formulas allow us to create our schedule period by period, to know how much we will pay monthly in principal and interest, and to know how much is left to pay.

Creating a Loan Schedule

To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods.

In the first period column, enter "1" as the first period and then drag the cell down. In our case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals 120.

The second column is the monthly amount we need to pay each month—which is constant over the entire loan schedule. To calculate the amount, insert the following formula in the cell of our first period:

=-PMT(TP;B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)

The third column is the principal that will be repaid monthly. For example, for the 40th period, we will repay $945.51 in principal on our monthly total amount of $1,161.88.

To calculate the principal amount redeemed, we use the following formula:

=-PPMT(TP;A18;$B$4*12;$B$3) =-PPMT((1+3,10%)^(1/12);1;10*12;120000)

The fourth column is the interest, for which we use the formula to calculate the principal repaid on our monthly amount to discover how much interest is to be paid:

=-INTPER(TP;A18;$B$4*12;$B$3) =-INTPER((1+3,10%)^(1/12);1;10*12;120000)

The fifth column contains the amount left to pay. For example, after the 40th payment, we will have to pay $83,994.69 on $120,000.

The formula is as follows:

=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)

The formula uses a combination of principal under a period ahead of the cell containing the principal borrowed. This period begins to change when we copy and drag the cell down. The table below shows that at the end of 120 periods, our loan is repaid.

Источник: https://www.investopedia.com/articles/personal-finance/082415/schedule-loan-repayments-excel-formulas.asp

Mortgage Payment Calculator with Taxes and Insurance

Calculator Use

Calculate your total monthly mortgage payment.  When calculating a new mortgage where you know approximately your annual taxes and insurance, this calculator will show you the monthly breakdown and total. This is a good estimate; when keeping taxes and insurance in an escrow account the payment charged by your financial institution could be different.

For a simple calculation without insurance and taxes, use this mortgage calculator without taxes and insurance.

Mortgage Amount
the original principal amount of your mortgage when calculating a new mortgage or the current principal owed when calculating a current mortgage
Mortgage Term
the original term of your mortgage or the time left when calculating a current mortgage
Interest Rate
the annual nominal interest rate or stated rate on the loan. Note that this is the interest rate you are being charged which is different and normally lower than the Annual Percentage Rate (APR).
Taxes
If your financial institution will be keeping an escrow account, billing you, and handling the payment of your property taxes then include that yearly amount here.
Insurance
If your financial institution will be keeping an escrow account, billing you, and handling the payment of your property insurance then include that yearly amount here.
Monthly Payment
the payment amount to be paid on this mortgage on a monthly basis toward principal & interest, taxes and insurance.

 

Follow CalculatorSoup:
find CalculatorSoup and become a fan on facebookfollow CalculatorSoup updates on twitter
Источник: https://www.calculatorsoup.com/calculators/financial/mortgage-payment-calculator.php

Excel mortgage payment calculator formula -

I am a professional computer analyst for a large accountancy firm. In my spare time, I write about video games and various other topics.

The PMT function within Excel is a financial function that is used to calculate loan payments (it can also be used to calculate the value of investments over time). With the associated IPMT and PPMT functions, it is easy to create a worksheet that will calculate an amortization schedule for a mortgage or other loan.

These functions give you the ability to model different loan situations, including additional principal payments, different loan amounts, different interest rates, etc., and calculate monthly total payments as well as payments over the life of the mortgage.

This article will provide a step-by-step guide on how to create a mortgage loan calculator using Microsoft Excel. While the worksheet has been designed in Microsoft Excel 2007, the functionality will be the same on Microsoft Excel 2003.

Creating the Format for the Mortgage Calculator

Setting up the initial format of the Mortgage Calculator
Setting up the initial format of the Mortgage Calculator
Adding the formula =-PMT(C3/12,C4,C2) to calculate the Total Monthly Payment

Setting up the initial format of the Mortgage Calculator

Explanation of Financial Functions Used in Mortgage Calculator

PMT, IPMT, & PPMT Functions - for a loan using a constant interest rate and payment:

  • PMT calculates the total payment.
  • IPMT calculates the interest payment for a given period.
  • PPMT calculates the principal payment for a given period.

Syntax of these functions:

  • =PMT(rate, number of payments, present value, [Future Value], [type])
  • =IPMT(rate, period, number of payments, present value, [Future Value], [type])
  • =PPMT(rate, period, number of payments, present value, [Future Value], [type])

Where:

  • Rate: the monthly interest rate for the loan.
  • Period: the period for which you want to calculate the interest or principal.
  • Number of payments: the total number of payments of the loan.
  • Present value: the current value that the future payments are worth now.
  • Future Value: the future value you want to attain after last payment. Leaving this out will assume the last value is zero.
  • Type: indicates when the payments are due. (0 is end of period, 1 is beginning of period.)

The process to create a mortgage schedule in Microsoft Excel is relatively simple, although it does use some complex financial functions.

Start off by creating a simple worksheet where the data will be entered and the amortization will be calculated. In this example, add the basic variables such as loan amount, number of months, interest rate, etc., and the headings for the actual amortization schedule. To do this:

  • Create a range with the main loan details: Total Loan, Interest Rate, Months. These are the variables that will allow for modeling of different loan terms.
  • Add Total Monthly Payment, Total Payments and Total Interest Paid to this range – these will be calculated later.
  • Create the actual schedule by adding heading: Month, Opening Balance, Principal, Interest, Extra Payment, Closing Balance and Total Payment. Once formulas are added, this section will automatically calculate the amortization schedule based on the variables input in the main loan range.
  • Add a loan amount, and interest rate and the number of months to the range. This is done to ensure that the formulas are working and can be any value.
  • Format the headings and range.
  • Add the formula =-PMT(C3/12,C4,C2) to cell C5 (See explanation of PMT on the right) IMPORTANT NOTE: the interest in the main range is an annual interest. Divide this value by 12 to get the monthly interest rate used in the formula)

The basic format is complete. Other items that could be added (see above screenshot for an example of a more complex final worksheet):

  • PMI payment
  • Monthly Insurance Payment
  • Property Taxes

Adding the Initial Formulas to the Mortgage Calculator

Now that the initial format is set, the remaining formulas in the summary range and the top row in the amortization schedule can be created. In each cell referenced below, enter the relevant formula.

CellFormulaComment

C6

=SUM(H10:H500)

Totals all the 'Total Payment' rows to give an overall payment made (principal and interest).

C7

=SUM(E10:E500)

Totals all the 'Interest' rows to give an overall interest payment made.

B10

1

As this is the first payment, this is set as 1.

C10

=+C2

As this is the first payment, this is set as the loan value.

D10

=-PPMT($C$3/12,B10,$C$4,$C$2)

Calculates the principal payment for this period only.

E10

=-IPMT($C$3/12,B10,$C$4,$C$2)

Calculates the interest payment for this period only.

F10

 

No value assigned yet.

G10

=+C10-D10-F10

Calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only.

H10

=+D10+E10+F10

Calculates the total monthly payment (principal plus interest).

Adding the Final Formulas to the Mortgage Calculator

The first row of formulas has been created. Now the rest of the formulas can be created. They essentially are the same but check to see if the month is blank (the month checks to see if the prior closing balance is zero and returns blank if it is, otherwise it returns the value of the prior month plus one. In each cell referenced below, enter the relevant formula.

CellFormulaComment

B11

=+IF(G10>1,IF(B10="","",B10+1),"")

This checks to see if there is a Closing Balance first; then it checks to see if the month prior has a value; if both values or true then it adds one to the prior month's value.

C11

=+IF(B11="","",G10)

If the value of the Month is not blank then this uses the value from the prior month Closing Balance, otherwise it leaves the cell blank.

D11

=IF(B11="",0,-PPMT($C$3/12,B11,$C$4,$C$2))

If the value of the Month is not blank then this calculates the principal for this period only, otherwise it places zero in the cell.

E11

=IF(B11="",0,-IPMT($C$3/12,B11,$C$4,$C$2))

If the value of the Month is not blank then this calculates the interest for this period only, otherwise it places zero in the cell.

F11

No value assigned yet

G11

=IF(B11="",0,+C11-D11-F11)

If the value of the Month is not blank then this calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only, otherwise it places zero in the cell.

H11

=IF(B11="",0,+D11+E11+F11)

If the value of the Month is not blank then this calculates the total monthly payment (principal plus interest, otherwise it places zero in the cell.

Copy these formulas down to row 500. The basic mortgage calculator is now complete. The amortization schedule will automatically recalculate when the values in cells C2, C3 and C4 are amended.

Adding Extra Payments to the Mortgage Calculator

Once the design is complete you can amend any of the loan details to model the amortization; additionally you can add Extra Payments throughout the entire loan. In the example an extra payment has been applied to the loan every month. This extra payment reduces the length of the loan, the total paid and the interest paid. This extra payment can be made at any time during the loan and doesn’t have to be monthly.

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Comments

Michelle Dee from Charlotte, NC on September 11, 2012:

Very useful and this looks like it took a lot of hard work to put this hub together - it looks great and thanks for sharing, voted up and across (except funny).

chrissieklinger from Pennsylvania on July 21, 2012:

Very helpful; I had no idea you could do that in Excel 2007. Better route to go than using online web calculators.

Источник: https://turbofuture.com

Learning Outcomes

  • Use financial functions and formulas

Excel provides specific formulas and functions to assist with financial calculations. We will cover the top five most often used financial functions. The following scenario will inform the next example of financial functions.

A person secured a loan from a bank to purchase an apartment at an annual interest rate of 6%, over 20 years, with monthly payments. The loan has a present value (PV) of $100,000 (amount of new loan) and a future value (Fv) of 0 (because the goal of the loan is to have it completely paid off at the end of the time period). For the monthly payments use 6%/12 = 0.5% for Rate, and 20*12 = 240 for Nper (total number of payment periods). If only annual payments were made, then it we would use 6% for the Rate and 20 for Nper.

Payment (PMT)

Payment terms for a loan or investment. The Excel formula for it is =PMT(rate,nper,pv,[fv],[type]). This assumes that payments are made on a consistent basis.

Follow these steps to find the monthly payment amount for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab, Financial button, scroll until you find PMT in the drop-down menu.
    Excel screenshot of dropdown menu for Financial formulas in Excel's Formulas tab. Highlighting the PMT option.
  3. A dialog window asking for the inputs opens. Click on the cells matching the correct information for that field and the data will be entered into the formula automatically. Using the cell location instead of typing in the straight data, means the formula will automatically update if new data is entered into those cells. For the last two areas (Fv,Type) for loans, Fv can be omitted (or enter 0) and Type can also be left empty. Type left empty assumes that payments come due at the end of the period.
    Excel screenshot of PMT formula function argument dialog box. Each of the field required to the formula are being filled in.
  4. After the calculation, the monthly loan payment is $716.43. The figure is red because it is a debt paid against the total loan. (If you wish to have it not show as a red/negative number, type in a minus sign before the B2 Rate (-B2) and the PMT will show as a black, positive number.) The second screenshot shows the PMT formula and the corresponding cells associated with the formula. To see the formula correspondence, double clicking on the cell. This correspondence association works with any formulas used.
    Excel screenshot of the PMT data and calculated amount.Excel screenshot the PMT data after double clicking on the formula cell. All of the corresponding cells associated within the formula have colors corresponding to their place in the formula.
  5. With the formula established, when you change the rate, present value or nper, you can see the change in monthly payment it will take to pay off the loan.

Number of Periods (NPER)

Number of periods per loan or investment. The Excel formula for this is =NPER(rate,pmt,pv,[fv],[type]).

Follow these steps to find the number of periods for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab, Financial button, scroll until you find NPER in the drop-down menu.
  3. A dialog window asking for the inputs opens. Enter the corresponding cell locations.
    Excel screenshot of dropdown menu for Financial formulas in Excel's Formulas tab. Highlighting the NPER option.Excel screenshot of NPER formula function argument dialog box. Each of the field required to the formula are being filled in.
  4. The Nper is 240 months (20 years of monthly payments). The second screenshot shows the NPER formula and the corresponding cells associated with the formula. Double click on the cell to see its corresponding cells.
  5. With the formula established, changing the monthly payment amount shows the change in the number of periods needed to pay off the loan.
Excel screenshot of the NPER data and calculated amount.Excel screenshot of NPER data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

Rate (RATE)

The interest rate for a loan or a rate of return required to attain a specific amount on an investment over a period of time. The Excel formula for this is =RATE(nper,pmt,pv,[fv],[type],[guess]). The “guess” for our purposes can be left out for this scenario.

Follow these steps to find the interest rate for this loan:

  1. Enter all the information into a table.
  2. Using the Formulas tab > Financial button > Scroll until you find RATE in the drop-down menu.
  3. Dialog window opens > Enter the corresponding cell locations.
  4. The Rate is 0.50%. The second screenshot shows the Rate formula and the corresponding cells associated with the formula. Double click on the cell to see its corresponding cells.
Excel screenshot of the RATE data and calculated amount.Excel screenshot of RATE data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

Present Value (PV)

Present value of a loan or investment based on a constant interest rate (like a mortgage or loan). The Excel formula for this is =PV(rate,nper,pmt,[fv],[type]).

Let’s change the scenario a little to find a PV for a different loan. If you know that the monthly payments are $952.69, the interest rate is 6%, and the life of the loan is 20 years, what is the present value of that loan?

Follow the same steps to insert the formulas like before to find the present value for this loan.

Excel screenshot of the PV data and calculated amount.Excel screenshot of PV data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

After filling in the values for the above scenario the PV for this loan is $120,999.66 or if rounded up $121,000.

Future Value (FV)

Future value of an investment assuming constant, periodic payments with a constant interest rate. The Excel formula for this is =FV(rate,nper,pmt,[pv],[type]).

Let’s change the scenario again and find FV for a different loan’s time frame. If you know that the monthly payments are $895.54, the interest rate is 5%, and the life of the loan is 20 years, is the future value of that loan payed down to 0 in that 20-year time frame?

Follow the same steps to insert the formulas like before only using the FV function to figure this out.

Excel screenshot of the FV data and calculated amount.Excel screenshot of FV data after double clicking on the formula cell. The formula cells have colors corresponding to their place in the formula.

The answer is that, yes, the future value of the loan comes to zero in the timeframe.

Practice Question

Now you know how to use these five financial functions and formulas in Excel.

Contribute!

Did you have an idea for improving this content? We’d love your input.

Improve this pageLearn More

Источник: https://courses.lumenlearning.com/wm-computerapplicationsmgrs-2/chapter/financial-functions-and-formulas/

Schedule Loan Repayments With Excel Formulas

Loan repayment is the act of paying back money previously borrowed from a lender, typically through a series of periodic payments that include principal plus interest. Did you know you can use the software program Excel to calculate your loan repayments?

This article is a step-by-step guide to setting up loan calculations.

Key Takeaways:

  • Use Excel to get a handle on your mortgage by determining your monthly payment, your interest rate, and your loan schedule.
  • You can take a more in-depth look at the breakdown of a loan with excel and create a repayment schedule that works for you.
  • There are calculations available for each step that you can tweak to meet your specific needs.
  • Breaking down and examining your loan step-by-step can make the repayment process feel less overwhelming and more manageable.

Understanding Your Mortgage

Using Excel, you can get a better understanding of your mortgage in three simple steps. The first step determines the monthly payment. The second step calculates the interest rate, and the third step determines the loan schedule.

You can build a table in Excel that will tell you the interest rate, the loan calculation for the duration of the loan, the decomposition of the loan, the amortization, and the monthly payment.

Calculate the Monthly Payment

First, here's how to calculate the monthly payment for a mortgage. Using the annual interest rate, the principal, and the duration, we can determine the amount to be repaid monthly.

The formula, as shown in the screenshot above, is written as follows:

=-PMT(rate;length;present_value;[future_value];[type])

The minus sign in front of PMT is necessary as the formula returns a negative number. The first three arguments are the rate of the loan, the length of the loan (number of periods), and the principal borrowed. The last two arguments are optional, the residual value defaults to zero; payable in advance (for one) or at the end (for zero) is also optional.

The Excel formula used to calculate the monthly payment of the loan is:

= PMT((1+B2)^(1/12)-1;B4*12;B3)=PMT((1+3,10%)^(1/12)-1;10*12;120000)

Explanation: For the rate, we use the monthly rate (period of rate), then we calculate the number of periods (120 for 10 years multiplied by 12 months) and, finally, we indicate the principal borrowed. Our monthly payment will be $1,161.88 over 10 years.

Calculate the Annual Interest Rate

We have seen how to set up the calculation of a monthly payment for a mortgage. But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan. For that reason, we would like to know the corresponding annual interest rate.

As shown in the screenshot above, we first calculate the period rate (monthly, in our case), and then the annual rate. The formula used will be RATE, as shown in the screenshot above. It is written as follows:

=RATE(Nper;pmt;present_value;[future_value];[type])

The first three arguments are the length of the loan (number of periods), the monthly payment to repay the loan, and the principal borrowed. The last three arguments are optional, and the residual value defaults to zero; the term argument for managing the maturity in advance (for one) or at the end (for zero) is also optional. Finally, the estimate argument is optional but can give an initial estimate of the rate.

The Excel formula used to calculate the lending rate is:

=RATE(12*B4;-B2;B3) = RATE(12*13;-960;120000)

Note: the corresponding data in the monthly payment must be given a negative sign. This is why there's a minus sign before the formula. The rate period is 0.294%.

We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294 %) ^ 12-1 to obtain the annual rate of our loan, which is 3.58%. In other words, to borrow $120,000 over 13 years to pay $960 monthly, we should negotiate a loan at an annual 3.58% maximum rate.

Using Excel is a great way of keeping track of what you owe and coming up with a schedule for repayment that minimizes any fees that you might end up owing.

Determining the Length of a Loan

We will now see how to determine the length of a loan when you know the annual rate, the principal borrowed, and the monthly payment that is to be repaid. In other words, how long will we need to repay a $120,000 mortgage with a rate of 3.10% and a monthly payment of $1,100?  

The formula we will use is NPER, as shown in the screenshot above, and it is written as follows:

=NPER(rate;pmt;present_value;[future_value];[type])

The first three arguments are the annual rate of the loan, the monthly payment needed to repay the loan, and the principal borrowed. The last two arguments are optional, the residual value defaults to zero. The term argument payable in advance (for one) or at the end (for zero) is also optional.

=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)

Minus Sign Before the Formula

The corresponding data in the monthly payment must be given a negative sign. This is why we have a minus sign before the formula. The reimbursement length is 127.97 periods (months in our case).

We will use the formula = B5 / 12 = 127.97 / 12 for the number of years to complete the loan repayment. In other words, to borrow $120,000, with an annual rate of 3.10% and to pay $1,100 monthly, we should repay maturities for 128 months or 10 years and eight months.

Decomposing the Loan

A loan payment is composed of principal and interest. The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods.

The table above shows the breakdown of a loan (a total period equal to 120) using the PPMT and IPMT formulas. The arguments of the two formulas are the same and are broken down as follows:

=-PPMT(rate;num_period;length;principal;[residual];[term])

The arguments are the same as for the PMT formula already seen, except for "num_period," which is added to show the period over which to break down the loan given the principal and interest. Here's an example:

=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3) = PPMT((1+3,10%)^(1/12)-1;1;10*12;120000)

The result is shown in the screenshot above "Loan Decomposition" over the period analyzed, which is "one;" that is, the first period or the first month. We pay $1,161.88 broken down into $856.20 principal and $305.68 interest.

Loan Computation in Excel

It is also possible to calculate the principal and interest repayment for several periods, such as the first 12 months or the first 15 months.

=-CUMPRINC(rate;length;principal;start_date;end_date;type)

We find the arguments, rate, length, principal, and term (which are mandatory) that we already saw in the first part with the formula PMT. But here, we need the "start_date" and "end_date" arguments also. The "start_date" indicates the beginning of the period to be analyzed, and the "end_date" indicates the end of the period to be analyzed.

Here's an example:

=-CUMPRINC((1+B2)^(1/12)-1;B4*12;B3;1;12;0)

The result is shown in the screenshot "Cumul 1st year," so the analyzed periods range from one to 12 of the first period (first month) to the twelfth (12th month). Over a year, we would pay $10,419.55 in principal and $ 3,522.99 in interest.

Amortization of the Loan

The prior formulas allow us to create our schedule period by period, to know how much we will pay monthly in principal and interest, and to know how much is left to pay.

Creating a Loan Schedule

To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods.

In the first period column, enter "1" as the first period and then drag the cell down. In our case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals 120.

The second column is the monthly amount we need to pay each month—which is constant over the entire loan schedule. To calculate the amount, insert the following formula in the cell of our first period:

=-PMT(TP;B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)

The third column is the principal that will be repaid monthly. For example, for the 40th period, we will repay $945.51 in principal on our monthly total amount of $1,161.88.

To calculate the principal amount redeemed, we use the following formula:

=-PPMT(TP;A18;$B$4*12;$B$3) =-PPMT((1+3,10%)^(1/12);1;10*12;120000)

The fourth column is the interest, for which we use the formula to calculate the principal repaid on our monthly amount to discover how much interest is to be paid:

=-INTPER(TP;A18;$B$4*12;$B$3) =-INTPER((1+3,10%)^(1/12);1;10*12;120000)

The fifth column contains the amount left to pay. For example, after the 40th payment, we will have to pay $83,994.69 on $120,000.

The formula is as follows:

=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)

The formula uses a combination of principal under a period ahead of the cell containing the principal borrowed. This period begins to change when we copy and drag the cell down. The table below shows that at the end of 120 periods, our loan is repaid.

Источник: https://www.investopedia.com/articles/personal-finance/082415/schedule-loan-repayments-excel-formulas.asp

Microsoft Excel can be used to create a mortgage calculator. The mortgage calculator can be used to calculate the monthly payment on a mortgage based on different variables for loan amount, interest rate and the term of the loan. Here are the steps to create a mortgage calculator in Excel:

Step 1: Create Headings

Create headings in different cells for loan amount, interest rate, loan term, payments per term and payment amount. Headings should be placed in column 1 in the order listed.

Step 2: Format Cells

The cells containing values go in column 2. The cells containing loan amount and payment amount need to be for currency. The interest rate should be a percentage. Loan term and payments per term are integers.

Step 3: Give Cells a Name

The first cell in Excel is A1. You can replace this name with something else. For example, you could replace the names of your cells with the following names: Loan_Amount, Interest_Rate, Loan_Years, and Num_Pmt_Per_Year.

Step 4: Create Formula

The payment amount cell will contain the formula. The formula will use a built-in function called PMT. This function uses input from the other fields to calculate the monthly mortgage payment. Using the cell names above, enter the following formula in the payment amount cell:

=-PMT( Interest_Rate/Num_Pmt_Per_Year,Loan_Years * Num_Pmt_Per_Year,Loan_Amount)

Источник: https://www.finweb.com/real-estate/real-estate-tips-building-an-excel-mortgage-calculator.html

Are you planning to take a loan? If so, what is your monthly payment would be? Will you be able to afford that payment? What is the best repayment period for you? You can easily find answers to these questions by performing a few easy calculations on spreadsheets. In this post, I will show you how you can Calculate Loan Repayments with Google Sheets.

For these calculations, you need to know the bank interest rate on advances of your bank for your intended loan type.

Calculating Monthly Loan Repayment Amount

You can calculate the monthly payment amount directly from the Google Sheet function .

: The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.

Let;

  • Annual Interest Rate is 6%
  • Your loan amount (PV) is $40,000
  • The repayment period is 7 years

You can apply the above values to the function as bellow, then you will get the monthly payment amount.

Finding the best combination

Depend on your repayment capacity, your requirement, and the interest rate you need to find the best combination of interest rate, repayment period, and loan amount. You can perform a what-if analysis using the above function as demonstrated in the following image to find the best combination. You can use the conditional formatting option to highlight values based on your limitations.

Calculating the amount of principal and interest for each month

In the above section, we calculated the total payment you need to pay to the bank each month. However, your principal and interest are not the same each month. You can calculate these principal and interest payments using using and equation.

: The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate.

: The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate.

With the function you can calculate the principal amount for a specific month and the relevant interest component is given by the function. The sum of the and components is the total amount that you have to pay each month. By subtracting the first-month principal from the loan amount you can get the balance principal amount after the first month. Subtracting the second-month principal amount from the first month’s balance you can get the balance principal amount after the second month. Likewise, you can calculate the balance principal amount after each month.

In the following example, I am calculating the monthly principal amount, monthly interest rate, monthly installment, and balance principal amount for a loan amounting to $40,000 with a 6% annual interest rate and 7 years repayment period.

By combining the above calculations, you can find the optimum loan amount and the best interest rate and repayment period to match your requirements. You can save your workings in your Google Drive and use it whenever you required to calculate loan repayments.

You can make a copy of the Google Sheets with the above workings to your drive from this link

Related

Categories SpreadsheetsTags Google SheetsИсточник: https://www.bpwebs.com/how-to-calculate-loan-repayments-with-google-sheets/
excel mortgage payment calculator formula
excel mortgage payment calculator formula

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *