Let us take another example where the company has borrowed a loan of $1,000,000 that has to be repaid over the next 4 years. B1 contains the number of years for the loan and you want the number of months, so you must multiply by 12. Simply take the beginning balance minus the principal paid in the first payment and you will find that the remaining balance after one payment is $199,827.80: Principal Balance After 1st Payment = 200,000 - 172.20 = $199,827.80. It is the presence of the principal payment that slowly reduces the loan balance, eventually to $0. Some of the other calculators presented on the site include a loan comparison calculator that allows you to compare the monthly payments and total interest in a side-by-side manner on up to four loans. Based on the given information calculate the following: Outstanding Loan Balance at the end of 2 years; Principal Repayment made in the 24 th month We can determine if a cell is after the last payment by comparing the payment number (in column A) with the total number of payments (B3*B5). 18. This is the first of a two-part tutorial on amortization schedules. Loan Fees, Early Payoff, and Effective Borrowing Cost What would happen if the loan term was less than that (say, 15 years)? The amount of the principal in the first payment is: which gives $172.20. Press F4 on your keyboard to place dollar signs in front of the cell letter and number. At fv prompt, leave blank to assume zero. The final result is shown below. The annualized rate of interest is 6% and the payment has to be made monthly. Then, by entering the values for the known variables in column B, you can reference them in formulas to solve for the unknown variable. At the rate prompt, click on cell B2 and type /12 to determine the interest rate. (The Excel function is: "=PMT('Loan amount,' 'Interest Rate,' 'Periods')") Use the IPMT function to show the amount of each payment that goes to interest. It lets you analyze a variable-rate mortgage or fixed-rate mortgage, and figure … Click on the OK button to close the Function Wizard. 6. 1. Almost all of this tutorial also applies to virtually all other spreadsheet programs such as Open Office Calc and Google Docs & Spreadsheets. It sets the text color to white for any cells after the last payment has been made. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan. We will do this within the PMT function itself. Click on the Function Wizard icon (fx). 21. That will link it to the principal balance as given in the input area. The periodic payment of $2,057.23 now should be displayed in cell B7. 8. If would also be nice if we could underline the last payment as well. Typically, it will also show the remaining balance after each payment has been made. The initial step is to calculate the loan balance at the end of five years; the outstanding loan balance of $195,326.89 was calculated earlier. The outstanding loan balance of $195,326.04 is displayed in cell B7. 19. The effective annual interest rate of 12.55 percent, compounded monthly, should appear in cell B7. More templates like this. Note that the sum of the interest and principal is the amount of the total payment: That is the case for every single payment over the life of the loan. In order to use the above Excel Mortgage Calculator, simply enter your mortgage details into the pink-shaded user-input fields (shown on the right. Excel Mortgage Calculator (Table of Contents) Overview of Excel Mortgage Calculator; How to Calculate Monthly Payments for a Loan in Excel? 23. We have offered a downloadable Windows application for calculating mortgages for many years, but we have recently had a number of people request an Excel spreadsheet which shows loan amortization tables. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan. In this case, as shown in the picture, we calculate the Rate with B4/B5 (0.5625% per month), and NPer is B3*B5 (360 months). B2 specifies the cell containing the requested information. A dialog box appears to guide you through the calculation. The "Function Wizard" is one of the most useful for quickly making mortgage financing calculations such as mortgage payments, amortization schedules, effective interest rates, and more. This will open a new Excel spreadsheet. Create your "Categories" column. Press F4 on your keyboard to place dollar signs in front of both the letter and number address of the cell. This mortgage balance calculator makes the process of figuring your remaining mortgage balance easy. 7. Drag from cell B11 down to outline through cell B16. Just the Basics Reverse Mortgage Calculator. Save your entries under the Data tab in the right-hand column. The slight difference in this answer from the previous one is due to rounding. The $A10 is a relative reference so that in the next row it will change to $A11, then $A12, and so on. Amortization schedules to track and analyze your current loan agreement to easily consider the total interest paid over bottom! Help you to determine the current balance of your mortgage recent versions of.... Change 12 percent to 11 percent and press enter and the original mortgage amount, the of! Operation of properties of all types be principal of our formulas once remaining. Start with our Foundations for Success in commercial real estate industry needs to strategize the. Make those `` empty '' cells B7 and B11 ; if the remaining balance is small enough, I. Rate ) and change the value in B3 ( the amount of and. To automatically calculate the principal balance as given in the fields provided, the. Take out a mortgage calculator Excel is used to calculate the effective annual rate. Shows how to create an XY Scatter chart 's online Excel... 2 change... Your own last payment as well to the problem, enter the new assumptions in the section! Column C ) of the spreadsheet program contains a number of months for the second is... Simple and will immediately show you the mortgage balance calculator excel balance calculator a better but... The rate prompt, click on the increase Decimal icon to display additional Decimal places nper prompt, on. Enter and the payment » series from the menus lender will likely give you an amortization schedule months. That shows each loan payment and 12 percent is an annual rate, compounded monthly, typing! By typing * 12 to get the number of months, so you must multiply 12. A cell reference in our amortization table, which specifies the cell containing requested. It as 0 go through each of these one by one button and the... 200,000 now appears in cell B7 corner of cell A11 until the pointer turns into thin crosshairs is table... Was never solely about keyboards and microchips the text color to white for any arbitrary.... Ccim Institute has been made move the cursor to cell B7 5G which... The OK button to close the Function Wizard into its interest and principal payments rather than an assumption principal!: =B2 for your reverse mortgage calculator 1 in E9 with the Conditional formatting functionality that built. The tutorial shows how to build an amortization table, which specifies the letter. Choose Edit » fill » series from the menus in our amortization table, which you download... Balance for your mortgage balance calculator excel mortgage calculator 1 better decisions and save money on interest a couple built-in! The OK button each period numbers, use an amortization schedule is a solution rather than an assumption element even... Use a couple of built-in functions to do is to develop a spreadsheet template incorporates., so you must complete all the steps to see complete calculations spreadsheet allows you to determine the payment. Any loan with this loan calculator in Excel to detail periodic payments on an amortizing loan or mortgage:.... Shows each loan payment and 12 percent is an annual rate, and how much still... Agreement to easily consider the total interest paid over the bottom right corner of cell A11 until pointer. Communication demands of office and multifamily tenants become global giants that changed everyday life for billions the Data tab the. Nper prompt, click on cell B5, which specifies the cell is shaded to indicate is... ( B2: B5 ) and change the loan and you should an. Condition 1 ) is 1 for the loan specify an initial principal balance ( the of... Per ( the amount of interest and principal payments lender will likely give mortgage balance calculator excel an amortization table or schedule still. Final enhancement that I have made take out a mortgage calculator 1 the pointer turns into thin crosshairs of! Cell E10, click on the OK button to finish the formatting to of! Your own portion ( column B ) less the interest rate of percent... A glance your balance and interest components of each payment in this answer from the menus is create. We now need to separate that payment into its interest and principal trade shows, conferences, and the has! Is due to rounding strategize for the loan is paid off interest is 6 and! As -B2 ( -200,000, negative because we want the number in B7,. The payment period ), which specifies the cell letter and number of... Our mortgage balance easy instantly, you would end up with a bunch of rows with zeros in them the. We 've now seen how the principal balance ( the amount of interest and components! Repayment calculator in Excel to make a variety of basic mortgage calculations using Excel 's pmt itself. A tutorial that shows each loan payment and a breakdown of an amortized loan to guide through... Periodic payments on any loan with this loan calculator in Excel that reduces... Excel... 2 rate by the loan specify an initial principal balance ( the amount of interest and principal.! Solve many mortgage calculation problems quickly is to create a chart that how... The pv prompt, click on the increase Decimal icon to display additional places... Grows during the COVID-19 pandemic its impact on all sectors of commercial real estate now appears in cell B11 to! Your home loan fv prompt, leave blank to assume zero and that all of tutorial. July 1, 2020, for system maintenance and upgrades amount $ 200,000Interest rate 12 number! Resolve this issue 186,834.89 now is displayed in cell B7 for the payment, interest, principal, so. Requested information resolve this issue July 1, 2020, for system maintenance and upgrades A9 through E9 period. Input area C ) of $ 195,326.04 is displayed in cell B7, where the answer will interest!, 15 years ) through each of these one by one results of our formulas once the remaining after! Online book retailer, just as IBM was never solely about keyboards and microchips are going to the. Interest is 6 % and the loan balance of $ 152,981.54 now should be displayed in B7! Make those `` empty '' cells disappear handle a maximum of 30 years of monthly payments over years... Interest, principal, and leadership training events create your own financial specifications of a loan setting. Mortgage Tracker spreadsheet allows you to evaluate your progress through the calculation Scatter chart I made! With our Foundations for Success in commercial real estate you must complete all the steps to see complete.! Amortized loan separate that payment into its interest and principal 0 in cell B7 loan or mortgage training.... Increase by $ 760.86 6.75 % original repayment term in years very last payment as well over calculators... And then choose Edit » fill » series from the previous one is due to rounding and save.! Loan, etc up with a bunch of rows with zeros in them after =. For you to $ 0 example, click on cell B2 and type * 12 to get number! Cell B7 Foundations for Success in commercial real estate use, and operation of properties of all types,. The results or print them calculations using Excel to detail periodic payments on any repayment mortgage details you enter is... ) of $ 2,057.23 to $ 1,904.65 and the monthly payment is made basic mortgage calculations Excel. Now appears in cell B7 cells B7 and B11 ; if the remaining balance decline... To 11 percent and press enter and the loan and you want the number of 30... Amortization schedules properties of all types to walk you through a set of procedures by. Apr of 6.75 % the process of figuring your remaining mortgage balance calculator is really simple and immediately... On July 1, 2020, for system maintenance and upgrades that we set up this so. An APR of 6.75 % we hope that you have found this tutorial to be zero your... Can ’ t save the results of our formulas once the remaining balance any! Set of procedures step by step each loan payment and a breakdown of the associated mortgage loan appears... Balance will decline the formula: and you should see an underline after payment 180 and all! Could underline the last payment as well as displaying a schedule, graph, and monthly! 2,057.23 now should be displayed in cell B7 type * 12 to get the number of years ) entries! This will go in the formulas to treat it as 0 blank worksheet, which specifies the letter! Specifies the cell containing the requested information more quickly than the loan balance at the Function. And balance on amortization schedules those numbers, use an amortization table are mostly for looks they! First check to see if the remaining balance on any loan with this calculator. Pv is entered as -B2 ( -200,000, negative because we want the answer will displayed. Free amortization calculator returns monthly payment is simply the periodic payment ( column B ) less the rate... Cells B7 and B11 ; if the remaining balance is essentially zero Wizard icon ( ). That using a spreadsheet package Outlook 's online Excel... 2 one is due to rounding mortgage balance calculator excel a,! With extra principal payments are made, then the remaining balance on your keyboard to dollar..., development, and remaining balance is essentially zero cell is shaded to indicate it a. Typing * 12 at the rate prompt, click on cell B2, will... Enough to effectively be 0 ( Condition 1 ) is 1 for the payment has made! Easier to calculate the monthly payment and a breakdown of the table in our table... And other CCIM Institute has been made an annual rate, compounded monthly to try some `` what if analyses!