![]() In my example, they differ by 2.73E-12, an infinitesimal and usually insignificant difference due to binary arithmetic User You might try it both ways, just to see if you can get a match.)Īlso note that the "min pmt" in D68 and D78 should the same, or nearly so, if the interest rates in D66 and D76 are the same. I always use ROUNDUP, not ROUND (typo), for the payment to ensure that the last payment is less. Or you could try the most common remedy: use =ROUND(PMT(.),2) in D68 and D78. If it does not match and you want help to understand why not, I will need actual numbers for D75, D78, D80 and D64. Verify that that matches your actual numbers. The new loan in D64 is the remaining balance of the original loan after the number of payments in D80. 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. 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. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |