• Registered: 2014-08-02
  • Posts: 911

Topic: amortization table

Hello everyone
At the request of a friend who wanted a simple application to know
the amount of the monthly payments of his loan, I made this application to him (MVD 6.1).
Everything is fine, MVD does the job (see attachment).
Now my friend would like me to add the amortization table for these calculations.
Now I'm having trouble getting there.
I was thinking of using a DbGrid to display the data (Sequential number of monthly payments, Monthly payment, decreasing loan amount, decreasing interest amount and balance)
My problem: as I am not using a table, I cannot fill in the Table and Fields fields.
My question: does MVD allow you to fill a grid with values other than those of a table and if so how to achieve this?
If not, I would go with Delphi 7 or Lazarus.
Thank you in advance for your opinions
J.B.

Post's attachments

Attachment icon AMORTISSEMENT.ZIP 43.31 kb, 32 downloads since 2024-04-08 

2 Reply by sparrow (edited by sparrow 2024-04-08 20:12:43)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Hi,

both options are possible
Here is an example for SQLite.
Example1 - script query, Example2 - Buttom SQL query.
Replaced SQLite DLL with a new version.


Example of calculation taking into account days in a month.
I would like to draw special attention to the fact that bank calculations may differ.

Post's attachments

Attachment icon amorti-m.zip 541.07 kb, 35 downloads since 2024-04-08 

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Fixed and optimized SQLite queries.
Supports leap years in calculations.
Examples are linked to the program.

Post's attachments

Attachment icon amorti-m1.zip 608.14 kb, 43 downloads since 2024-04-09 

  • Registered: 2014-08-02
  • Posts: 911

Re: amortization table

Hi Sparrow
It's exactly what I expected.
One question: why we don't have the same value for monthly repayment:
€302.99 for me and €936.64 for you (whether with Example Script or Example SQL)
and this even if I don't use the Round?
JB

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Hi,


936.64 in my first attached file is for a loan of 100,000, rate 10, period 240 months. with the date of issue 01/01/2010.
According to your formula with such data, the payment is 965.02.
Our formulas are different and the calculation of the monthly rate from the annual rate is different. I just gave an example. I'm not saying that my formula is better or the result is more accurate. And different banks may have different calculations. In any case, the most accurate result is the one that the bank will calculate and which will have to be paid))))). wink Oh, those banks.


As an option, look at a bank calculator (only without various commissions). and compare with our formulas.
As for the formula, I will help you enter your formula.

  • Registered: 2014-08-02
  • Posts: 911

Re: amortization table

Hi Sparrow
Sorry, my question doesn't make sense.
I hadn't read the header of your code (loan borrowed =100000) and rate =10
Thanks again
J.B.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

But there is still a discrepancy.

  • Registered: 2014-08-02
  • Posts: 911

Re: amortization table

Hello Sparrow
But when I went online to find the correct calculation formula, I noticed some discrepancies. In addition, I did not take into account insurance costs, I only took into account interest at a constant rate. But as it suited my friend as it was, I didn't go any further.
By the way, a question: in your SQLQuery code, is it possible to intersperse values contained for example in a TEdit?
You have to explain: you initially defined a sum of €100,000 as the amount of the loan. This amount comes up frequently in your code. Is it possible to ensure that the value of this sum is replaced by the one that the user enters in the TEdit? Same thing for the value of the annual rate?
Thank you for your answers
Cordially
JB

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Hi Jean,


A little higher in the post I posted the amorti-m1.zip version.

Here is the direct link. https://myvisualdatabase.com/forum/misc … download=1


Everything there is already tied to the EditBox where you can set your values.
Indicate your final formula and I will add it instead of mine. Yesterday I looked at calculators online and they all calculated using your formula.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Hi Jean,


The calculation formula has been changed to A=P*(r(1+r)^n)/((1+r)^n-1)

Post's attachments

Attachment icon amorti-m2.zip 607.83 kb, 36 downloads since 2024-04-11 

  • Registered: 2014-08-02
  • Posts: 911

Re: amortization table

Hi Sparrow
It works perfectly.
Your new code is terribly effective.
I thank you again
Good luck at home!
Kind regards
J.B.

12 Reply by sparrow (edited by sparrow 2024-04-21 18:57:03)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: amortization table

Hi Jean,


Now I can fully answer your first question.
Calculation of annuity and differentiated payments.
Calculations are made:
- SQLite recursive
- Recursive script function
- Loop in script procedure
The script contains commented code for working directly with TableGrid with a brief description.
Ability to output results to a report or Excel.

Post's attachments

Attachment icon amorti-shed.zip 590.73 kb, 30 downloads since 2024-04-21 

  • Registered: 2014-08-02
  • Posts: 911

Re: amortization table

Hello Sparrow
Very impressive !
Thank you for coming back with new features.
Very instructive.
Thank you so much
Kind regards
J.B.