Applying an "Additional Payment" to Amortized Loan Schedule Table

[Todd W Carter]

I am using the attached file to calculate amortization table for clients. However, there is a glitch in the app somewhere that does not allow the balance to be visible to the calculations in the loop. If I enter a loan for any amount and set the term to 30 years, at payment number 161 the balance is no longer visible or usable which creates problems for the following payment schedule. At payment 161 I can click in the field and it appears the correct balance is there, but it cannot be seen when I leave the field or used in calculations...

This is a file I found here on the forum in a 2014 discussion... So I am looking for help to debug it.... 

Anny help will be appreciated and thanks in advance...

 

Amortization Table with Additonal Payment Option.zip

[comment]

I am not particularly eager to debug someone else's file - esp. as it is not well-written (at least IMHO) and the logic is not clear. Still, I took a look at it and I suspect the problem you report is caused by the Balance field auto-entering a calculated value =

Balance - AdditionalPayment & Round (Balance-.009; 2)

I cannot find the logic in this. The field is defined as a Number field (as it well should), and concatenating two numbers makes no sense.

Similarly, the Principal field auto-enters =

Repaid + AdditionalPayment & Round (Repaid - .005; 2)

This causes both fields to contain more than one decimal point. I did not spend time investigating how exactly this leads to the breakup you see. However, when I commented out the parts that start with the & symbol, the breakup disappeared. I am not sure what effect this has on the correctness of the result - but I did not check the correctness of the result before making this change either.

I would advise you to rewrite the file from scratch. And take this opportunity to make it much simpler. If you're using a script to create the schedule (as you should), then place the entire logic in the script itself and make it populate the fields in the Schedule table with their final values - without requiring the fields to perform additional calculations. Also, I am not sure what exactly is achieved by subtracting .005 or .009 before rounding. I recall seeing this in some old files, but I could never understand the reason for it.

 

[Todd W Carter]

Thanks for your assistance... Once I commented the & stuff, the application runs very nicely... 

As you said, I do not understand the concatenation either. But now that it is gone, I won't worry about it...

Again thanks very much, it was a puzzle to me....

[comment]

1 minute ago, Todd W Carter said:

now that it is gone, I won't worry about it...

In your place I would worry about one thing: are the results correct?

 

[Todd W Carter]

Yes they are correct...

Edited March 23, 2021 by Todd W Carter