Loan calculator with irregular payments

How much of this is financial math and how much is Excel programming? Usually, if you will help us understand the financial math involved, we can help program those calculations into the spreadsheet.

If you need help with the financial math, then there may be a few users here with enough financial expertise to assist. It seems to me (as a non-expert) that there is some significant information missing from the problem statement, such as how often interest accrues on the loan. I would also expect to need to know something about other specific loan terms, such as late penalties or specifically how the lender will handle extra payments.

I don't know if it helps, but I entered =PMT(D5/12,18,D4,0) into a cell, and the function for the expected monthly payment would be 1831.55, which matches your first few monthly payments. If I use that to assume that the original loan terms were "standard" monthly payments, then I would probably expect to start building my amortization table using the "standard" monthly payment amortization table. Then adjust the standard amortization table for the missed payments, non-standard payments, and extra payments. Assuming that is an appropriate programming model, this tutorial was the first result in my internet search https://www.exceldemy.com/excel-amor...ular-payments/ I note that all of their examples of "irregular" payments are additional payments, and they don't include any examples with missed payments. If you understand the financial math involved, that tutorial may be useful in organizing your calculations in the spreadsheet.

As near as I can tell, there is a lot of questions about the original terms of your loan and the expected amortization schedule and exactly how the lender will treat deviations from the original schedule. Help us understand the financial calculations that are supposed to happen here, and we should be able to help you program those calculations into the spreadsheet.

I'm sure it can be done, but I don't know the equations or formulas or algorithms for such calculations. As I said, if you help us understand the equations and calculations that go into this, we should be able to help you program those calculations into the spreadsheet. If you, too, are uncertain of the financial principles, then we can only wait until someone more knowledgeable about such calculations comes and explains them to us.

After a couple of days without further input, are you figuring out what you need to do? In the absence of further input, I used Exceldemy's amortization table (tab named Irregular Extra Payment) to consider possibilities. Steps I took:

1) Download and open ExcelDemy's file in the Irregular Extra Payment tab.
2) Fill in the "User Inputs" section (D4:D13) according to the loan information in your file.
3) Enter "extra" payment information in column E according to the payment schedule in your file.
3a) Oct, Nov, Dec payments were standard payments, so leave E21:E23 blank
3b) Jan payment has an extra 293.15, so enter 293.15 into E24
3c) Feb payment has 293.15 less than standard payment, so I enter -293.15 into E25.
3d) Mar to Jun payments were missed, so I enter =-1*payment into E26:E29
3e) July's payment was 4 times standard, so I enter 3*payment into E30. Continue adding extra payment information in column E.
4) I notice that the spreadsheet "errors" with the first negative principal value in column G, so I modify the formula in H so that it doesn't check if G is less than 0. =IFERROR(H20-G21,"") entered into H21 and then copy/paste/fill down.

Even if the loan terms assumed by ExcelDemy are different from your loan terms, it should show that it is possible to do an amortization table in Excel with irregular payments.