I have a DB that has all of our clients and their payment (monthly, weekly or bi-monthly), the interest rate, the amount of the loan and the day of the 1st payment.
I want to include in that form a field that would calculate the value left of their loan based on all the information. Is there a way to get the field to provide the amount left when the form is opened or get an amortization chart created automatically in Notes that would provide the details of what’s left to paid.
To calculate the interest (calculated daily) I need to use this formula:
Amount of loan * interest rate / 365 (days in a year) X number of days between payments. That will provide me with the monthly interest paid on the loan and the capital (payment - interest). Every month I need to recalculate what’s left since the interest will be lower since the amount of the loan is reduced.
If you have fields that hold the Loan Amount, Current Balance and Interest Rate, then it’s just a matter of mathematics. The tricky part would be figuring out the days between payments, if this is not a constant (i.e. every 30 days).
What part is giving you trouble? you seem to have the formula already.
The interest between the days is not the end of the world, in a perfect system it would calculate it but the closest estimate is perfect.
The part that I have a problem is calculating it, since I need it for let’s say month 36 out of 72 it would either be to do a script to do the operation 36 times.
Even the creation of the amortization table is tricky for me as the only way I thought of doing it would be to create a field for each capital/interest/total per month so if a client has a lease of 84 months paying weekly that gives me over 350 fields which i’m sure there is a better way to do it.
Okay- just read your response- Notes is excellent at having multivalue fields - so, you can w/ one field calculate the iteration of payments. This formula (with two fields) calculates all the dates in between two dates in to one mulivalue field for a calendar view. (This particular usage is for a vacation calendar)
Range := @TextToTime(@Explode(@TextToTime(@Text(StartDate) + “-” + @Text(EndDate))));
what if each of those fields (capital, interest, total) were multi-value fields which first subtracted the current month with the lease end month, then did the calculation for their corresponding values… So for example, this would give you a capital field with 36 values, each value being the capital for the corresponding month. Do the same for the other two calculations in the other two fields. Now you have three fields with all the per month values you need. You can then create an agent to create an html table in a rich text field.
Maybe something like this (not tested):
Dim doc As NotesDocument
Dim rtitem As NotesRichTextItem
'…set value of doc…
Dim rtitem As New NotesRichTextItem(doc,“amortization”)
totalmonths = UBound(capitalfield.values)
Call rtitem.AppendText(“
”)
For x = 0 to (totalmonths) ’ 0 because the array index starts at 0
It helps a lot Paul, your db helped me understand how to do the calculation and with that I should be able to include the information in the form of the client and to get his actual loan amount left within a field.
I don’t see any payment type @functions- you might embedd an excel spreadsheet w/ some lotusscript to populate the appropriate values. The Excel formulas have the date issue programatically in them.