Here is one approach. If you're not familiar with the XL2BB add-in, you can read about it on this site. To copy this working example to your workbook, click on the clipboard icon in the upper left (intersection of rows and columns) and note the upper left of the displayed cell (A1 in this case). Go to a blank worksheet, and simply paste into that same cell (again, here it is A1).
I'll explain what the sheet is doing. The upper left blue cells are obvious...you enter known details of the original mortgage. The original baseline monthly payment is calculated in C11. Then suppose the customer calls you and says that they still owe 15119 after making payments for 25 months (see cells C15:C16). You run a basic amortization schedule and conclude that they must have been making extra payments, but the details are not known. For this example, suppose they began paying an extra 250 beginning with payment #20 (so payments 1-19 were at the baseline amt, and payments 20-25 were at baseline + 250)...see cells C20:C24 where this is input. I tend to make these computations using some "old school" methods, and this particular computation for the amount owed after some month t2 is given in cell C25. That formula assumes one adjustment to the payment schedule and relies on basic financial compounding factors (more of that below). As a check on the formula in cell C25, we have a long-form amortization schedule A shown at right (columns G:K), and we see that for whatever month is specified in cell C24, we'll find that cell C25 matches the amount in column K corresponding to the same month.
Now to determine the equivalent uniform overpayment amount, we repeat a similar process in cells E20:E25, except your only inputs are cells E23:E24. In this case, you would enter 25 for both...meaning that some uniform payment exceeding the baseline was applied from t0 through t1 (and t1 = t2 = 25 months). A slightly revised financial formula in E22 computes the monthly payment such that after payment t1 (or t2), the same amount owed on the loan is obtained as the client reported. Cell E21 calculates the amount of the overpayment. Amortization schedule B (columns M:P) provides confirmation that this is indeed the case. And cell E22 represents the quantity you want. With this approach, you don't really need the amortization schedules if you don't want to look at them.
As for the financial compounding factors, I used user-defined functions shown below. I find these simpler to use because they're a little more intuitive and require only two inputs (a yearly interest rate and the period of interest). If you choose to implement these, add them to a module in your workbook (right click on sheet name, select View Code, navigate to your workbook and select Module or possibly Insert a Module, and then paste the following, then Save and Return to Excel. You don't need all of these, as the formulas I used refer to only two of them (and three are reciprocals of the others, any one can be expressed as a function of another two, so only two of these are really necessary to form the others...but having all six is handy). These assume payments occur at the end of a compounding period, and that payment frequency and compounding frequency are the same and occur monthly.
I should clarify the meaning of the function names: F is Future Value, P is Present Value, and A is the Amount of some payment. So PgivenA means the factor that relates P to A...
or mathematically, P = A * PgivenA
VBA Code:
Function AgivenP(IntRt As Double, Pd As Double) As Double
AgivenP = ((IntRt / 12) * (1 + IntRt / 12) ^ (Pd * 12)) / ((1 + IntRt / 12) ^ (Pd * 12) - 1)
End Function
Function PgivenA(IntRt As Double, Pd As Double) As Double
PgivenA = ((1 + IntRt / 12) ^ (Pd * 12) - 1) / ((IntRt / 12) * (1 + IntRt / 12) ^ (Pd * 12))
End Function
Function FgivenP(IntRt As Double, Pd As Double) As Double
FgivenP = (1 + IntRt / 12) ^ (Pd * 12)
End Function
Function PgivenF(IntRt As Double, Pd As Double) As Double
PgivenF = 1 / (1 + IntRt / 12) ^ (Pd * 12)
End Function
Function FgivenA(IntRt As Double, Pd As Double) As Double
FgivenA = ((1 + IntRt / 12) ^ (Pd * 12) - 1) / (IntRt / 12)
End Function
Function AgivenF(IntRt As Double, Pd As Double) As Double
AgivenF = (IntRt / 12) / ((1 + IntRt / 12) ^ (Pd * 12) - 1)
End Function
