But I wanted to keep the formulas in the lefthand table simple, like what I thought you might be used to.) The only difference is the pmt expression: $D$68 in column G and $D$68+$D$69 in column L. I also replaced the formulas in columns F:I to be similar to the more flexible formulas in columns K:N. Then I redefined the data in C64:D71 for the new loan. You might need to modify that formula, since your description ``we are say 8 yrs 3 mos`` is vague. But I was able to update the attached file.)Īs you can see, I copied the relevant data for the original loan into C75:D78.Īnd I added D80, which calculates the number of pmts made already. (Aarrgghh! The lefthand table title has a copy-and-paste error (it should be "without" not "with"), and I am inexplicably unable to replace the image the way I want to. See the attached Excel file for formulas. Thanks wrote: ``we are say 8 yrs 3 mos into the amortization schedule of a 30 yr loan`` Let me know if you have an idea of how to accomplish this. I apologize again for not being clearer but I hadn't thought through what I actually needed from a formula thoroughly enough. I have done this before in a broadly broken out column by column layout but is there a way to accomplish the same thing in the same neat consolidated format as your first solution? If we are taking over financing midstream the allocation of principle and interest will be at a different point than if we began adding principle on day one of a 30 yr loan for instance. The exact amount is not important but the method of calculating a way point at any given point in time as we will intervene at a different points in the existing loan cycle every time. The calculation I need will illustrate my taking over that loan 8 yr 3 mos into its life and in this illustration, at that point adding additional principle of a a given amount. The scenario is where there is an existing loan in place and we are say 8 yrs 3 mos into the amortization schedule of a 30 yr loan. My use for the formula you came up with has one nuance I didn't consider.
![excel mortgage calculator with extra payment excel mortgage calculator with extra payment](https://i.pinimg.com/originals/e6/ea/56/e6ea568b6ad554928549207a054f6cf0.png)
![excel mortgage calculator with extra payment excel mortgage calculator with extra payment](https://db-excel.com/wp-content/uploads/2019/01/mortgage-spreadsheet-with-regard-to-amortization-schedule-excel-free-calculator-india-loan-mortgage.png)
I just realized something that will likely change the approach to what I was attempting and I apologize for the detour in advance. The challenge to be answered by traditional spreadsheet methods is "does an excess of simplicity itself create impenetrable and error-prone solutions?" The end of the Einstein quote you mentioned was ". The idea is that such a formula is less prone to errors of consistency than a traditional formula copied across a range. All a Lambda function does is allow one to write a formula in terms of parameters passed to it as variables. You found the use of Lambda functions off-putting. The method is far closer to the world of professional programmer than it is to that of a normal spreadsheet end-user. The 'simplicity' I set out to achieve is to generate each table from a single formula rather than the original 2240 individual formulas.
![excel mortgage calculator with extra payment excel mortgage calculator with extra payment](https://ufreeonline.net/wp-content/uploads/2019/04/amortization-chart-with-extra-payments-lovely-excel-loan-amortization-schedule-with-extra-principal-of-amortization-chart-with-extra-payments.png)
![excel mortgage calculator with extra payment excel mortgage calculator with extra payment](https://www.mortgagecalculator.org/images/enable-editing.png)
The calculation produces an array of balance figures (columns H and M) and the other columns form no part of the calculation they are derived for information only. I accept that the calculation I presented is a mathematical abstraction of the problem and does not capture practical considerations no business practice is going to work with millionths of a cent.