Mortgage Calc - Question

MattHam44

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, I am making a mortgage Calculator with an amortization schedule.
I want to add a specific feature and cannot figure out how to write it.

You can add a function that allows you to see the amortization schedule when applying an extra monthly payment towards principal, which I have.
But I am wanting to have a formula that generates what the extra payment is given the original balance, current balance, and date it was started.
This will help me figure out on average what someone has paid extra towards principal throughout the loan so far.

To find the Remaining Balance each month I am using the formula, =SUM(x-p-e) where x is the original principal balance, p is the monthly principal, and e is the extra payment.

If someone has an idea on how to calculate an average amount someone has paid extra to bring the remaining balance down below what it should be in a given time frame, that would be great. Let me know if I need to provide any further details.
Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the Board? Could you clarify something, please? When you say "given the original balance, current balance, and date it was started", does this mean you know when the additional payment was begun, but you don't know the amount of the additional payment? Or do you not know two things: 1) the additional payment amount, and 2) when the additional payments were begun?
 

MattHam44

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board? Could you clarify something, please? When you say "given the original balance, current balance, and date it was started", does this mean you know when the additional payment was begun, but you don't know the amount of the additional payment? Or do you not know two things: 1) the additional payment amount, and 2) when the additional payments were begun?
Hey Thanks, Yes. So I only know what their current balance is, the initial balance, and the starting date. Also rate and term.
I will notice sometimes the current balance doesn't match up with where they should be on the schedule, so I have to assume they have made extra payments sometime throughout the term.
The extra payment may not be every month, and maybe not the same amount, so I am wondering if there is a way to find an "average" extra payment that would align their current balance with the loan schedule up to date.

Hope that helps, let me know if you need more clarification.
Thanks
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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


Book1
ABCDEFGHIJKLMNOP
1Mortgage: extra payment analysis
2
3Pinitial principal50000Amort Sched AAmort Sched B
4nterm3y
5iinterest rate8.50%/ymopmtadd'l pmtbefore pmtafter pmtpmtadd'l pmtbefore pmtafter pmt
6pmt freq12/y05000050000
7mcmpding pds12/y11578.377050354.16748775.791634.417050354.1748719.75
8pmt occurs end of pd21578.377049121.28547542.9081634.417049064.8547430.43
931578.377047879.6746301.2931634.417047766.446131.98
10Orig Mortgage Monthly Pmt41578.377046629.26145050.8841634.417046458.7544824.33
11Apmt1578.376871($1,578.38)51578.377045369.99543791.6181634.417045141.8443507.42
1261578.377044101.80842523.4311634.417043815.642181.18
13Suppose payment amount was increased by 250 at month 2071578.377042824.63941246.2621634.417042479.9640845.55
14client reports:81578.377041538.42339960.0461634.417041134.8739500.45
15amt owed on principal15119.5390591578.377040243.09738664.721634.417039780.2538145.83
16after25mo101578.377038938.59537360.2181634.417038416.0336781.61
17111578.377037624.85336046.4761634.417037042.1535407.73
18Amort Sched AAmort Sched B121578.377036301.80534723.4281634.417035658.5434024.12
19"Unknown" to 2nd lender Equiv. uniform overpmt131578.377034969.38633391.0091634.417034265.1232630.71
20A (pmt t0 thru t1)1578.3768711578.376871141578.377033627.52932049.1521634.417032861.8431227.42
21addl25056.04011412151578.377032276.16730697.791634.417031448.6229814.2
22Pmt (t1+1) thru t21828.3768711634.416985161578.377030915.23329336.8561634.417030025.3928390.97
23t119mo25171578.377029544.65827966.2821634.417028592.0726957.65
24t233mo25181578.377028164.37626585.9991634.417027148.625514.19
25Amt owed after t21003.0313515119.53905191578.377026774.31725195.941634.417025694.9124060.5
26201578.37725025374.41123546.0341634.417024230.9222596.51
27211578.37725023712.81921884.4421634.417022756.5721122.15
28221578.37725022039.45620211.081634.417021271.7619637.35
29231578.37725020354.24118525.8651634.417019776.4418142.03
30241578.37725018657.08916828.7131634.417018270.5316636.12
31251578.37725016947.91615119.5391634.417016753.9615119.54
32261578.37725015226.63613398.2591634.417015226.6413592.22
33271578.37725013493.16311664.7861634.417013688.512054.08
34281578.37725011747.4129919.03511634.417012139.4610505.05
35291578.3772509989.29498160.9181634.417010579.468945.04
36301578.3772508218.72456390.34771634.41709008.4017373.984
37311578.3772506435.61264607.23581634.41707426.2165791.799
38321578.3772504639.87032811.49351634.41705832.8244198.407
39331578.3772502831.40821003.03141634.41704228.1462593.729
40341578.3772501010.1362-818.24071634.41702612.101977.6842
41351578.377250-824.0366-2652.4131634.4170984.6095-649.807
42361578.377250-2671.201-4499.5781634.4170-654.41-2288.83
MattHam44
Cell Formulas
RangeFormula
K6,P6K6=$C$3
O7:O42,J7:J42J7=K6*(1+$C$5/12)
P7:P42,K7:K42K7=J7-(H7+I7)
C11C11=C3*AgivenP(C5,C4)
D11D11=PMT(C5/C7,C6*C4,C3)
C20,E20C20=$C$3*AgivenP($C$5,$C$4)
C22C22=C20+C21
E21E21=E22-E20
E22E22=(C3-$C$15/FgivenP($C$5,E23/12))/PgivenA($C$5,E23/12)
C25C25=($C$3-C20*PgivenA($C$5,C23/12)-C22*(PgivenA($C$5,C24/12)-PgivenA($C$5,C23/12)))*FgivenP($C$5,C24/12)
E25E25=($C$3-E22*PgivenA($C$5,E23/12))*FgivenP($C$5,E24/12)
H7:H42H7=$C$11
M7:M42M7=$E$22
 
Last edited:

MattHam44

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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


Book1
ABCDEFGHIJKLMNOP
1Mortgage: extra payment analysis
2
3Pinitial principal50000Amort Sched AAmort Sched B
4nterm3y
5iinterest rate8.50%/ymopmtadd'l pmtbefore pmtafter pmtpmtadd'l pmtbefore pmtafter pmt
6pmt freq12/y05000050000
7mcmpding pds12/y11578.377050354.16748775.791634.417050354.1748719.75
8pmt occurs end of pd21578.377049121.28547542.9081634.417049064.8547430.43
931578.377047879.6746301.2931634.417047766.446131.98
10Orig Mortgage Monthly Pmt41578.377046629.26145050.8841634.417046458.7544824.33
11Apmt1578.376871($1,578.38)51578.377045369.99543791.6181634.417045141.8443507.42
1261578.377044101.80842523.4311634.417043815.642181.18
13Suppose payment amount was increased by 250 at month 2071578.377042824.63941246.2621634.417042479.9640845.55
14client reports:81578.377041538.42339960.0461634.417041134.8739500.45
15amt owed on principal15119.5390591578.377040243.09738664.721634.417039780.2538145.83
16after25mo101578.377038938.59537360.2181634.417038416.0336781.61
17111578.377037624.85336046.4761634.417037042.1535407.73
18Amort Sched AAmort Sched B121578.377036301.80534723.4281634.417035658.5434024.12
19"Unknown" to 2nd lender Equiv. uniform overpmt131578.377034969.38633391.0091634.417034265.1232630.71
20A (pmt t0 thru t1)1578.3768711578.376871141578.377033627.52932049.1521634.417032861.8431227.42
21addl25056.04011412151578.377032276.16730697.791634.417031448.6229814.2
22Pmt (t1+1) thru t21828.3768711634.416985161578.377030915.23329336.8561634.417030025.3928390.97
23t119mo25171578.377029544.65827966.2821634.417028592.0726957.65
24t233mo25181578.377028164.37626585.9991634.417027148.625514.19
25Amt owed after t21003.0313515119.53905191578.377026774.31725195.941634.417025694.9124060.5
26201578.37725025374.41123546.0341634.417024230.9222596.51
27211578.37725023712.81921884.4421634.417022756.5721122.15
28221578.37725022039.45620211.081634.417021271.7619637.35
29231578.37725020354.24118525.8651634.417019776.4418142.03
30241578.37725018657.08916828.7131634.417018270.5316636.12
31251578.37725016947.91615119.5391634.417016753.9615119.54
32261578.37725015226.63613398.2591634.417015226.6413592.22
33271578.37725013493.16311664.7861634.417013688.512054.08
34281578.37725011747.4129919.03511634.417012139.4610505.05
35291578.3772509989.29498160.9181634.417010579.468945.04
36301578.3772508218.72456390.34771634.41709008.4017373.984
37311578.3772506435.61264607.23581634.41707426.2165791.799
38321578.3772504639.87032811.49351634.41705832.8244198.407
39331578.3772502831.40821003.03141634.41704228.1462593.729
40341578.3772501010.1362-818.24071634.41702612.101977.6842
41351578.377250-824.0366-2652.4131634.4170984.6095-649.807
42361578.377250-2671.201-4499.5781634.4170-654.41-2288.83
MattHam44
Cell Formulas
RangeFormula
K6,P6K6=$C$3
O7:O42,J7:J42J7=K6*(1+$C$5/12)
P7:P42,K7:K42K7=J7-(H7+I7)
C11C11=C3*AgivenP(C5,C4)
D11D11=PMT(C5/C7,C6*C4,C3)
C20,E20C20=$C$3*AgivenP($C$5,$C$4)
C22C22=C20+C21
E21E21=E22-E20
E22E22=(C3-$C$15/FgivenP($C$5,E23/12))/PgivenA($C$5,E23/12)
C25C25=($C$3-C20*PgivenA($C$5,C23/12)-C22*(PgivenA($C$5,C24/12)-PgivenA($C$5,C23/12)))*FgivenP($C$5,C24/12)
E25E25=($C$3-E22*PgivenA($C$5,E23/12))*FgivenP($C$5,E24/12)
H7:H42H7=$C$11
M7:M42M7=$E$22
Thanks so much! I am going to work on getting this implemented.
This has been very helpful!
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Good...I'm happy to help. Let me know if you run into any difficulties. You should be able to achieve the same result using the built-in Excel functions rather than the compounding factors, although I still prefer the factors. If implementing the UDF's (user defined functions) is problematic (perhaps because of VBA restrictions in some corporate environments), you could just calculate them separately in some helper cells and then reference those cells with the formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,124
Messages
5,640,249
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top