Applying an "Additional Payment" to Amortized Loan Schedule Table

[Davisla58]

Hello!

 

First off I would like to apologize, I've taken a rather long hiatus from developing in FM and have picked it back up recently(within the past two weeks or so). So if my question seems "noob-ish", it probably is, as I am not really reacquainted with the program yet and am still trying to find my barrings. So some simple things are simply getting by me. With that being said I greatly appreciate the time anyone takes to try and answer my question an explain things to me.

 

Now, down to my question. I'll try to be a little more in-depth encase whomever is reading this is not really familiar with Amortized Loans. I have recently created a section in a database for a Amortized Loan Schedule. It's on a portal, the fields in this portal include: PaymentNumber, Payment (always the same number), DueDate, Interest, Principle, Balance. I have a scripted button that is used to generate the information in the portal. I have however run into a small snag. I now need to add an additional field within the portal for "AdditionalPayment" (it's a rare occurrence but I need for just encase purposes). This additional payment will not include interest and will be added directly to the Principle amount and deducted right from the Balance (it's used in the event that someone makes a extra payment to help pay off the loan faster, but does not effect the payment amount the following month). This additional payment will however effect the rest of the table as the payment is split between interest (annual interest rate/12 *Balance of the previous month), as the additional payment will effect the balance, it will effect the Interest, principle, and balance amount for the rest of the table rendering it incorrect. 

 

I need the additional payment field in the table as I need to be able to see what line it is on, therefore what payment number it's associated with. I would like another button scripted that *if* an additional payment is entered, I can click on it and will regenerate the table, adding the additional payment in and will correct the rest of the Loan schedule. 

 

I am however, at a complete loss at how to do this. I've played around with the scripting a bit but have yet to yield anything useful. 

 

If anyone needs a clone copy of my current file I will gladly supply it. My script is nothing special just based off of an old JMO file just altered to fit in the other things I need. 

 

Thanks everyone who took the time to read this and possibly respond. I really appreciate. 

 

[Lee Smith]

I just did this search of Google and got several hits.

site: fmforums.com Amortized Loan Schedule

 

HTH

 

Lee

[comment]

I would like another button scripted that *if* an additional payment is entered, I can click on it and will regenerate the table, adding the additional payment in and will correct the rest of the Loan schedule. 

 

You cannot regenerate the (entire) table, because by doing that you would delete the existing records - including the one that contains the extra payment.

 

You need to:

1. Delete the subsequent records in the current schedule (we are assuming here that extra payments are made ad hoc, so any future schedule records do not contain additional payments);

 

2. Initialize variables to:

  $principal = the balance of the current record (the one with the extra payment);

  $rate = the original annual interest rate / 12;

  $payment = the original payment (i.e. calculated from the original loan amount, interest rate and the number of periods);

 

3. Generate the rest of the schedule using the above variables. Note that since you want the monthly payment to remain the same, you cannot use the original number of periods here; you must loop until you deplete the principal.

[Davisla58]

Thanks for the reply's. 

 

Lee Smith, I looked at Google before posting and there are tons of Amortized Loan Schedule examples. However, I could not find any which also accounted for anything regarding an extra payment. If you found something I didn't and would be willing to post a link I would be grateful. Thanks so much for your replay. 

 

Comment, yes, I had assumed I couldn't regenerate the "entire" table. As like you said I would lose even the additional payment. So deleting subsequent records makes sense. You are also correct in your third point, the example I have with an additional payment in an excel doc actually does change the number of periods (originally there was 120 periods, but the additional payment changed it to 119 periods). 

 

I had it part-way last night, the additional value could be entered and it would add it to the "repaid" amount, but it wasn't taking the value off of the balance and correcting the rest of the table. I think I might have to come at this from a little bit of a different angle. I'll try to follow the explanation above, and see what I get as you pointed out some things I was missing.

 

I'll post anything I find and if I happen to get it working correctly I will also post an example encase anyone else is looking to add an additional payment into their own Amortized Loan Schedule.  

 

Thanks again! I will keep checking back while I am working on it today to see if anyone else has any other ideas. 

[Davisla58]

Okay, I'm still stuck on step one. Deleting the subsequent records.... 

 

I've tried to perform a find (to find the record with the additional payment on it, so say payment number 4), loop, go to related record/request/page [next] (to go to the following record, payment number 5), then delete record/request, end loop. Of course silly me, it deletes everything and not just to following records 5-whatever. I then remembered reading that the delete record/request will actually delete the master. Is this true? If so then it makes perfect sense that everything was then deleted as opposed to the following records. 

 

Go to layout [schedule]

Perform Find [schedule:AdditionalPayment > 1] 

Show all records

Go to related record/record/request/page [next] 

**This successfully gets me to correct record, which is the one after the after the additional payment is on**

delete record/request

Loop

Go to related record/record/request/page [next] 

delete record/request

End Loop

 

So I then tried to accomplish deleting the records via the portal with deleted Portal Record. However, I still have the same problem.... Everything gets deleted not just the next portal row. 

 

I know I'm missing something (duh right?), but I'm getting increasingly frustrated which is making me miss things that should normally be apparent. 

 

I'm attaching a file this time, sorry if it's a bit messy. 

 

 

 

New folder.zip

[comment]

How about:

Go to Related Record [ Show only related ; From: Schedule ]
Set Variable [ $recNum ; Get ( RecordNumber ) ]
Go to Record [ Last ]
Loop
  Exit Loop If [ Get ( RecordNumber ) = $recNum ]
  Delete Record/Request
End Loop

This would be called by clicking a button in the portal row that contains the extra payment.

[Davisla58]

Perfect. I had a small data modeling error (extra option clicked in the relationship section that I didn't notice) that wasn't allowing it to work at first, but I fixed it and the script worked perfectly. Now I just need to add on to that, so it will re-calculate the rest of the table as well. 

 

I'm *assuming* I can repeat my first script (that generates the whole table) but just make alterations for the changes due to the additional payment and the variables you mentioned above? 

[Davisla58]

Okay I'm back and I have two more things: 

1) I apparently need re-schooled in how the "Exit loof if" script works. As I have everything working perfectly expect for that the loop keeps running. I tried to Exit Loop If [$principal=0], Exit loop if [not $principal] and a few other options. The $principal (Schedule: Balance) is set to deplete. But when it reaches 0 it just keeps going into the negative, and on and on. 

 

The Script:

Go to related record [show only related records; "schedule"]

Set Variable [$recNum Value:Get (RecordNumber)

Go to record [Last]

Loop

  Exit loop if [Get(recordNumber)=$recnumber]

  Delete Record

End Loop

Set Variable [$loanID; Value: Loans::LoanID]

Set Variable [$principal; Value: Schedule::Balance]

Set Variable [$rate; Value: Loans::AnnualRate/12]

Set Variable [$payment; Value: Loans::cPayment] 

Loop

  Exit Loop If [?]

  New Record

  Set Field [schedule::LoanID; $loanID]

  Set Field [schedule::Interest; $principal *$rate]

  Set Field [schedule::RePaid; $payment- Schedule::Interest]

  Set Variable [$principal; Value:$principal-Schedule::RePaid]

  Set Field [schedule::Balance; $principal]

End Loop

Commit Records

 

Pretty sure I need another Set variable in there  for the $principal as well. But the few things I have tried haven't worked, they either off-set the whole table or seem to do nothing. 

 

2) Rounding. I have all fields set to decimal and to show two places. But everything is rounding up, when dealing with money you always round down. I tried setting it to currency thinking maybe FM would know that. But of course not. I did try: 

Round (Number-.005, 2) to see if I could get it to round "down" in a sense. But it didn't work. Can I possibly use and Int, or Floor Calculation? Will that work to round down to the second place after the decimal? I have tried it with, Int(Number*100)/100, but couldn't get it to do anything. But I can't think of another option. If there's a post somewhere on this and someone could re-post the link I would be thrilled. 

 

 

Thanks again!

 

 

 

[comment]

When you add an extra payment and leave the payment amount as is, the last payment will almost certainly be too high. You must make sure that the actual payment does not exceed the current balance. For this reason, your schedule table should also have a field for Payment, which would be populated as =

Min ( $payment ; $principal * ( 1 + $rate ) ) 

or, if you prefer =

Min ( $payment ; $principal + Schedule::Interest ) 

if you have already populated the Interest field.

 

 

Then adjust the Repaid calculation to use the actual Payment instead of $payment. That should prevent the balance from going into negative (I think). In order to prevent an extra payment of 0.00 due to rounding errors, exit the loop if =

Round ( $principal ; 2 ) = 0

.

 

 

 

when dealing with money you always round down.

 

I have never heard of such rule. I've heard of Bankers' rounding, but not "always round down". How is this supposed to work - does that apply only to halves, or do you want to round 0.009 down to 0 too?

[Davisla58]

That did work very well. Thank you. 

 

It's no longer going into the negatives and does stop. Once the Balance hit's 0. 

 

 

I have never heard of such rule. I've heard of Bankers' rounding, but not "always round down"

 

Then perhaps I was incorrect in thinking that. Apologies. 

 

It will round .009 down to 0 as well. 

 

For example: the excel sheet I have has rounding on. When I click it off the example sheet and my table match up perfectly. However, when it is on (which from what I understand for this accountant is per normal), I am off by cents within the second balance line, then the further it goes down, the more fields are off. Why I said yes to the above (.009 round down to 0 as well) is because from what I can tell every line looks to be rounded down, (example: second line the balance= 46718.4476, but the value on the Excel sheet is 46718.44, even though the 7 says it should round up, it doesn't. If I'm making sense).

 

Like the last payment on my table is 341.54, which does not add up to the excel example with rounding on which is 314.19. 

[Davisla58]

Also, if anyone needs this without the rounding feature, there is a zip file below with an example. 

Amortization Table with Additonal Payment Option.zip

[comment]

It will round .009 down to 0 as well.

 

This type of rounding can be achieved using either =

Floor ( number * 100 ) / 100

or =

Int ( number * 100 ) / 100

depending upon the desired behavior for negative numbers (round towards zero, or round towards negative infinity). However, I believe it is highly unorthodox to use it, especially when dealing with money. But then I am not a CPA nor an accountant, so...

[Davisla58]

 

 

However, I believe it is highly unorthodox to use it, especially when dealing with money.

 

That's my stand on it as well. 

 

Maybe I will script a check-box to turn the rounding on and off, like it has in excel. But I at least wanted to know how, so my example will match with the excel example. I will probably leave it off for now though.

 

 

Thanks, so much for all of your help I really appreciate it!