![]() Then, type the number of extra payments in cell E14.Remember, the extra payment frequency is only available for “weekly” and “monthly” payment frequencies. Moreover, for cell E15 it will be “=INDIRECT(payment_frequency)”. The source will be “=payment_due” for the cells E12 and E13. Similarly, repeat the above steps for the cells E12, E13, and E15.By doing so, we will see two items on the dropdown list.Then, select List inside the “ Allow:” box and type “=payment_types” as the source.Afterward, select cell E11 and press ALT, A, V, and then V one after another to bring up the data validation dialog box.Here, we can see the “periodic_table” named range on a hidden sheet, “Named Ranges”, and we have done this to keep users from accidentally changing values.Then, press ALT, M, and N (one after another) to bring up the Name Manager dialog box.Here, 1.5 years have passed after the loan disbursement, so we have typed 28.5 in the remaining years field. To begin with, type the following values.Without further ado, let us jump into the steps. Then, we will apply the IF, PMT, and VLOOKUP functions to our aid. We will use the Name Range and Data Validation features of Excel. We will type the loan details in this first step. Thirdly, we will find the payment summary to complete our objective for this article. Following that, we will calculate the payment schedule. We will show 3 quick steps to create a mortgage calculator with extra payments and a lump sum in Excel. Mortgage Calculator with Extra Payments and Lump Sum in Excel (With Easy Steps) Read More: Early Mortgage Payoff Calculator in Excel So, by just adding two lump sum payments of amounts of $10,000 and $15,000, adding $200 of extra payments every month, and depositing our regular payment, we saved a lot of time and a good amount of money. You can see that extra payments have been added in the 19th period (after we are done with a total of 18 payments). We have input all the information in my template. You will input these values directly under the column Extra Payment (Lump Sum) for the respective payment periods. Adding Lump Sum Payments: On the 13 th and 17 th periods, let’s add two lump sum payments, $10,000 and $15,000, respectively.For “Monthly” Extra Payment Frequency, you cannot choose Weekly or Bi-weekly frequencies. You can also choose Bi-monthly, Quarterly, Semi-annually, Annually. Extra Payment Frequency: So, you want to add your extra payment ($200) every month.So, in addition to your regular PMT payments, you want to pay an extra $200. Extra Amount You Plan to Add ($): 200.For different Interest Compounding Frequency & Regular Payment Frequency, Interest Compounding Frequency must be equal to or higher than the frequency of the Regular Payment. This is the normal case: Regular Payment Frequency will be the same as the Interest Compounding Frequency. Interest Compounding Frequency: Monthly.Regular Payment Frequency: It means you pay your regular payments every month.Remaining Years: 28.50, It means you have already paid the regular payments for the period 30-28.50 = 1.5 years.Suppose you have loan details like the following: Let me show you the use of this template with an example. You can choose a different payment frequency for your extra recurring payments.In some countries, for example, Canada, payment frequency and interest compounding frequency can be different. You can choose your Interest Compounding Frequency.Time saved (if you made recurring extra payments or irregular/lump sum extra payments).Estimated interest savings (if you made recurring extra payments or irregular/lump sum extra payments).The total amount paid over the lifetime of the loan.The template will show you the following outputs: Depositing irregular / lump-sum payments when you are able.Calculating your regular payments (PMT).You can use this calculator in three ways: ![]() We can assure you that this is the most versatile Excel calculator for a mortgage loan. Let me explain the terminology and usage of this template. We have developed an Excel template that will be the best tool in your journey to becoming debt-free. ![]() From Warren Buffet to Ray Dalio, all big investors emphasize living beyond your means and saving the rest. From big countries to small businesses, all are sunk in debt. Related Articles Using Mortgage/Loan Calculator with Extra Payments & Lump Sum in Excelīecoming a debt-free person is the best blessing in this age of the debt.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |