How to Create a Mortgage Calculator With Microsoft Excel

Rate this post

One of Excel’s biggest features is its ability to compute mortgage-related expenditures such as interest and monthly payments. Creating a mortgage calculator in Excel is simple, even if you are unfamiliar with Excel capabilities. This article will show you how to use Microsoft Excel to build a mortgage calculator and amortization plan.

You are viewing article How to Create a Mortgage Calculator With Microsoft Excel at website answerforyou.net.

Creating a Mortgage Calculator

1. Launch Microsoft Excel. If you don’t have Excel installed on your computer, you may replace it using Outlook’s online Excel extension. You may need to first establish an Outlook account.

2. Choose Blank Workbook. This will launch a new spreadsheet in Excel.

3. Make a “Categories” column. This will be placed in the “A” column. To begin, click and drag the separator between columns “A” and “B” to the right at least three spaces so that you don’t run out of writing space. For the following categories, you will require a total of eight cells:

  • Amount of the Loan Annual Interest Rate in $
  • Life Insurance (in years)
  • Payments made each year
  • Total Payments Payments Per Period Total Payments Interest Cost

4. Enter your values here. These will be placed in your “B” column, to the right of the “Categories” column. You’ll need to input the necessary mortgage values.

  • Your Loan Amount value represents the entire amount owed.
  • The value of your Annual Interest Rate is the proportion of interest that accrues each year.
  • Your Life Debt value is the number of years you have to pay off the loan.
  • The number of payments you make in a year is represented by your Number of Payments per Year value.
  • Your Total Number of Payments value is calculated by multiplying the Life Loan value by the Payments Per Year value.
  • The amount you pay each payment is represented by your Payment per Period value.
  • Your Sum of Payments value covers the whole loan cost.
  • The overall cost of interest throughout the life of the loan is determined by your Interest Cost value.

5. Determine the total amount of payments. You don’t need a formula to determine this number since it is your Life Loan value multiplied by your Payments Per Year value.
For example, if you make a monthly payment on a 30-year life loan, enter “360” here.

  How to Clean New Era Hats

6. Determine the monthly payment. Use the following formula to calculate your monthly mortgage payment: “= -PMT(Interest Rate/Payments per Year,Total Number of Payments,Loan Amount,0)”.

  • The formula for the attached screenshot is “-PMT(B6/B8,B9,B5,0)”. If your values vary slightly, enter them into the corresponding cell numbers.
  • Because PMT returns the amount to be deducted from the amount owing, you may use a minus sign in front of it.

7. Determine the total cost of the loan. Simply multiply your “payment per period” value by your “total number of payments” value to do this.
For example, if you make 360 payments of $600.00 each, your total loan amount would be $216.000.

8. Determine the total interest cost. Simply remove your original loan amount from the overall cost of the loan, which you computed earlier. After that, your mortgage calculator is finished.

Making a Payment Schedule (Amortization)

1. To the right of your Mortgage Calculator template, create your Payment Schedule template. Because the Payment Schedule relies on the Mortgage Calculator to determine how much you’ll owe/pay off each month, both should be included in the same document. Each of the following categories will need its own column:

  • Day – The date on which the payment is paid.
  • Payment (number) – The payment number as a percentage of the total number of payments (e.g., “1”, “6”, etc.).
  • Payment ($) denotes the entire amount paid.
  • Interest is the portion of the entire payment that is paid as interest.
  • Principal is the portion of the entire payment that is not interest (e.g., loan payment).
  • Extra Payment – The monetary value of any additional payments you make.
  • Loan – The amount remaining on your loan after a payment.

2. To the payment plan, add the initial loan amount. This should be entered into the first empty cell at the top of the “Loan” column.

3. Create the first three cells in the “Date” and “Payment (Number)” columns. In the date column, enter the date you took out the loan as well as the first two days you intend to make the monthly payment (e.g., 2/1/2005, 3/1/2005, and 4/1/2005). Enter the first three payment digits in the Payment field (e.g., 0, 1, 2).

4. To automatically insert the remaining Payment and Date entries, use the “Fill” function. You’ll need to do the following to do this:

  • Choose the first item in the Payment (Number) column.
  • Drag your mouse down until you reach the number that corresponds to the amount of payments you’ll make (for example, 360). You’d pull down to the “362” row since you’re beginning at “0.”
  • In the upper right corner of the Excel sheet, click Fill.
  • Choose a Series.
  • Check the “Linear” box in the “Type” section (when you do your Date column, “Date” should be checked).
  • Select OK.
  How to Know if a Hug Is Romantic

5. In the “Payment ($)” column, choose the first empty cell.

6. Fill in the Payment per Period formula. The formula for calculating your Payment per Period value is as follows: “Payment per Period,Total Loan+(Annual Interest Rate/Number of Payments per Year)),Payment per Period,Total Loan+(Annual Interest Rate/Number of Payments per Year)),Payment per Period,Total Loan+(Annual Interest Rate/Number of Payments per Year))”.

  • To finish the computations, you must precede this formula with the “=IF” tag.
  • Your “Annual Interest Rate,” “Number of Payments Per Year,” and “Payment Per Period” must be expressed as follows: $letter$number. As an example: $B$6
  • Given the screenshots here, the formula would look like this: “=IF($B$10<K8+(K8($B$6/$B$8)),$B$10,K8+(K8($B$6/$B$8)))” (sans the quotation marks).

7. Enter the code. This will apply the Payment per Period formula to the cell you’ve chosen.
You’ll need to utilize the “Fill” function you used before to apply this formula to all following cells in this column.

8. Choose the first blank cell in the “Interest” column.

9. Fill in the formula to calculate your interest value. The method for determining your interest value is as follows: “Total LoanAnnual Interest Rate/Number of Payments per Year.” To function, this formula must be preceded by a “=” symbol. The formula would appear like this in the screenshots: “=K8$B$6/$B$8″ (without quotation marks).

10. Enter the code. This will apply the Interest formula to the cell you’ve chosen.
You’ll need to utilize the “Fill” function you used before to apply this formula to all following cells in this column.

11. Choose the first blank cell in the “Principal” column.

12. Fill in the Principal formula. To use this calculation, just subtract the “Interest” value from the “Payment ($)” value.
For instance, if your “Interest” column is H8 and your “Payment ($)” cell is G8, you would type “=G8 – H8” without the quotation marks.

13. Enter the code. This will apply the Principal formula to the cell you’ve chosen.
You’ll need to utilize the “Fill” function you used before to apply this formula to all following cells in this column.

14. Choose the first blank cell in the “Loan” column. This should be equal to or less than the original loan amount (e.g., the second cell in this column).

15. Fill in the Loan formula. The loan value is calculated as follows: “Loan”-“Principal”-“Extra”.
You would put “=K8-I8-J8” without the quotation marks for the screenshots supplied.

16. Enter the code. This will apply the Loan formula to the cell you’ve chosen.
You’ll need to utilize the “Fill” function you used before to apply this formula to all following cells in this column.

  Best Used Car Sites

17. To finish your formula columns, use the Fill function. Your payment should be consistent throughout. The interest and loan amount should drop, but the principle values should rise.

18. Add the payment schedule together. Add the payments, interest, and principle at the bottom of the table. Compare these figures to the results of your mortgage calculator. If they match, the formulae were appropriately applied.

  • Your principle should be the same as the initial loan amount.
  • Your payments should correspond to the entire cost of the loan as calculated by the mortgage calculator.
  • Your interest rate should be the same as the interest cost calculated by the mortgage calculator.

Does Excel have a mortgage function?

PMT, a financial function, computes the loan payment based on continuous payments and a constant interest rate. To calculate a monthly loan payment, use the Excel Formula Coach.

How do I calculate 30 year mortgage in Excel?

=PMT(5 % /12,3012,180000) The rate argument is 5% divided by 12 months every year. For a 30-year mortgage with 12 monthly payments each year, the NPER is 3012. 180000 is the PV parameter (the present value of the loan).

What is the formula for mortgage calculation?

These elements include the total amount borrowed from a bank, the loan’s interest rate, and the period of time you have to pay off your mortgage in full. You’ll use the following equation for your mortgage calculation: M = P [i(1 + i)n] / [(1 + i)n – 1].

How do I calculate amortization in Excel?

Fill in the blanks with the correct values in cells B1 through B3. Enter the formula “=-PMT(B2/1200,B3*12,B1)” in field B4 to have Excel compute the monthly payment for you. For example, if you had a $25,000 loan with an annual interest rate of 6.5 percent for ten years, your monthly payment would be $283.87.

How do I calculate monthly interest on a loan in Excel?

Excel’s interest payment feature is called IPMT. It returns the interest amount of a loan payment in a particular period, assuming that the interest rate and total amount of a payment remain constant throughout all periods. …
=IPMT(6% /52, 1, 252, 20000) weekly =IPMT(6% /12, 1, 212, 20000) on a monthly basis
Quarterly:…
Semi-annual:

What is the formula for monthly payments in Excel?

If you make monthly payments on the same loan, the rate is 10% /12 or 0.00833. Nper (needed) – the number of loan payments, i.e. the total number of periods the loan should be paid over. If you make yearly payments on a 5-year loan, for example, enter 5 for nper.

Similar Posts