This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule

Givens: beginning balance, payment amount, # of periods, rate per
period
schedule would consist of columns for balance, principal paid,
interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period *
balance, row number
Can this be done in Access SQL?
Thanks,
Elaine 13 1870
Why not use a report? You can create running sums there without a
problem. I think you can create running sums in a query  I think John
Winterbottom posted the SQL some time ago. But it would be MUCH easier
to do in a report.
On Mon, 21 Mar 2005 14:15:08 0900, Elaine
<no******************@commerce.state.ak.us> wrote: This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
payment amount is superfluous. it can always be determined from the
other three, but for now let's assume that it is available.
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Are you willing to have a temporary table available for seeding the
rows? If so, let's presume that you want a recordset returned for a
loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where
temptable.payment_number < numberofperiods + 1
where:
numberofperiods = 60
expr1 is the balance
expr2 = payment_amount  rate_per_period * expr1
expr3 = payment_amount  expr2
So, this boils down to the determination of the balance at the
beginning of the period.
The balance is a simple mathematical equation that is a variation on
the formula I posted a couple of days ago.
Balance = payment_amount * ((1  (1+i)^n)/(i)) where:
i = rate_per_period
n = payments left (this is determined by number_of_payments 
payment_number
Depending on the version of access you are using, you can use a
disconnected recordset to create the seeds, so you can avoid the temp
table completely.
mike
Mike,
Payment amount is *not* superfluous unless one wants to recalculate
the payment amount for every row, which would be an awful lot of
calculations.
~~~~~
Here is a VB code fragment that produces an amortization schedule from
given parameters:
' establish beginning balance
fBal = pv
'
For i = 1 To fNPers
fInt = fRatePer * fBal
fPrinc = fPmt  fInt
If fBal < fPrinc Then ' adjust final payment amount
fPrinc = fBal
End If
Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc,
"Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i)
fBal = fBal  fPrinc
Next i
~~~~~
And, no, I don't want a temporary table. I want to know whether it is
possible to do this using SQL only, having the givens to procede from.
An ADO recordset would work swell, but, again, I am not interesting in
a solution which is not SQL only.
Thanks for your input.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike
Preston) wrote: On Mon, 21 Mar 2005 14:15:08 0900, Elaine <no******************@commerce.state.ak.us> wrote:
This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
payment amount is superfluous. it can always be determined from the other three, but for now let's assume that it is available.
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Are you willing to have a temporary table available for seeding the rows? If so, let's presume that you want a recordset returned for a loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where temptable.payment_number < numberofperiods + 1
where: numberofperiods = 60 expr1 is the balance expr2 = payment_amount  rate_per_period * expr1 expr3 = payment_amount  expr2
So, this boils down to the determination of the balance at the beginning of the period.
The balance is a simple mathematical equation that is a variation on the formula I posted a couple of days ago.
Balance = payment_amount * ((1  (1+i)^n)/(i)) where:
i = rate_per_period n = payments left (this is determined by number_of_payments  payment_number
Depending on the version of access you are using, you can use a disconnected recordset to create the seeds, so you can avoid the temp table completely.
mike
One of the basics of relational database design is that you do not store in
a record any value that can be calculated whenever it is needed, from other
values in that record. Retrieval time from rotating memory is so much slower
than calculations that you need not worry about recalculations affecting
performance.
Larry Linson
Microsoft Access MVP
"Elaine" <no******************@commerce.state.ak.us> wrote in message
news:f3********************************@4ax.com... Mike,
Payment amount is *not* superfluous unless one wants to recalculate the payment amount for every row, which would be an awful lot of calculations.
~~~~~ Here is a VB code fragment that produces an amortization schedule from given parameters: ' establish beginning balance fBal = pv ' For i = 1 To fNPers fInt = fRatePer * fBal fPrinc = fPmt  fInt If fBal < fPrinc Then ' adjust final payment amount fPrinc = fBal End If Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc, "Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i) fBal = fBal  fPrinc Next i ~~~~~
And, no, I don't want a temporary table. I want to know whether it is possible to do this using SQL only, having the givens to procede from.
An ADO recordset would work swell, but, again, I am not interesting in a solution which is not SQL only.
Thanks for your input.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike Preston) wrote:
On Mon, 21 Mar 2005 14:15:08 0900, Elaine <no******************@commerce.state.ak.us> wrote:
This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
payment amount is superfluous. it can always be determined from the other three, but for now let's assume that it is available.
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Are you willing to have a temporary table available for seeding the rows? If so, let's presume that you want a recordset returned for a loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where temptable.payment_number < numberofperiods + 1
where: numberofperiods = 60 expr1 is the balance expr2 = payment_amount  rate_per_period * expr1 expr3 = payment_amount  expr2
So, this boils down to the determination of the balance at the beginning of the period.
The balance is a simple mathematical equation that is a variation on the formula I posted a couple of days ago.
Balance = payment_amount * ((1  (1+i)^n)/(i)) where:
i = rate_per_period n = payments left (this is determined by number_of_payments  payment_number
Depending on the version of access you are using, you can use a disconnected recordset to create the seeds, so you can avoid the temp table completely.
mike
Larry,
From your response it is obvious that you did not read the question,
or if you did read it you did not understand it, or chose to ignore
the actual question and answer a question not asked.
I appreciate all the time you put in on the group, but taking a
subject offtopic is not appreciated.
Nowhere have I indicated that I am storing anything anywhere. Mike
seemed to assume that I asked a different question as well.
To repeat:
Do you know the SQL for generating an amortization schedule, given the
necessary values required for creating one, > USING SQL ONLY < ?
NO VBA, NO EXCEL FUNCTIONS, SQL ONLY
Thanks,
Elaine
On Thu, 24 Mar 2005 00:59:33 GMT, "Larry Linson"
<bo*****@localhost.not> wrote: One of the basics of relational database design is that you do not store in a record any value that can be calculated whenever it is needed, from other values in that record. Retrieval time from rotating memory is so much slower than calculations that you need not worry about recalculations affecting performance.
Larry Linson Microsoft Access MVP
"Elaine" <no******************@commerce.state.ak.us> wrote in message news:f3********************************@4ax.com.. . Mike,
Payment amount is *not* superfluous unless one wants to recalculate the payment amount for every row, which would be an awful lot of calculations.
~~~~~ Here is a VB code fragment that produces an amortization schedule from given parameters: ' establish beginning balance fBal = pv ' For i = 1 To fNPers fInt = fRatePer * fBal fPrinc = fPmt  fInt If fBal < fPrinc Then ' adjust final payment amount fPrinc = fBal End If Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc, "Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i) fBal = fBal  fPrinc Next i ~~~~~
And, no, I don't want a temporary table. I want to know whether it is possible to do this using SQL only, having the givens to procede from.
An ADO recordset would work swell, but, again, I am not interesting in a solution which is not SQL only.
Thanks for your input.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike Preston) wrote:
>On Mon, 21 Mar 2005 14:15:08 0900, Elaine ><no******************@commerce.state.ak.us> wrote: > >>This has to do with selfjoins and includes a running balance problem. >> >>Is it possible to do this using SQL alone? >> >>Amortization schedule >> >>Givens: beginning balance, payment amount, # of periods, rate per >>period > >payment amount is superfluous. it can always be determined from the >other three, but for now let's assume that it is available. > >>schedule would consist of columns for balance, principal paid, >>interest paid, payment number >> >>output calculation would be, effectively >> >>balance, payment  (rate per period * balance), rate per period * >>balance, row number >> >>Can this be done in Access SQL? > >Are you willing to have a temporary table available for seeding the >rows? If so, let's presume that you want a recordset returned for a >loan with 60 rows. > >Select expr1, expr2, expr3, payment_number from temptable where >temptable.payment_number < numberofperiods + 1 > >where: >numberofperiods = 60 >expr1 is the balance >expr2 = payment_amount  rate_per_period * expr1 >expr3 = payment_amount  expr2 > >So, this boils down to the determination of the balance at the >beginning of the period. > >The balance is a simple mathematical equation that is a variation on >the formula I posted a couple of days ago. > >Balance = payment_amount * ((1  (1+i)^n)/(i)) where: > >i = rate_per_period >n = payments left (this is determined by number_of_payments  >payment_number > >Depending on the version of access you are using, you can use a >disconnected recordset to create the seeds, so you can avoid the temp >table completely. > >mike >
BEGIN PGP SIGNED MESSAGE
Hash: SHA1
If it is necessary to run a loop to get the amortization sked, then, no,
you can't run a loop in Access' (JET) SQL. If you're using a db engine
that has stored procedures, like SQL Server, or Oracle, or IBM DB2, etc.
you can run a loop. That loop would store the results in a temp table &
then spew the results back to you in a recordset, deleting the temp
table as it finishes.

MGFoster:::mgf00 <at> earthlink <decimalpoint> net
Oakland, CA (USA)
BEGIN PGP SIGNATURE
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQkMce4echKqOuFEgEQJtlACg3Fnb4k8jY9GjJ+POvQXrtc 6fTVcAoNDW
Er28RvkoTVuRvjbuxIxhu470
=rlrr
END PGP SIGNATURE
Elaine wrote: Larry,
From your response it is obvious that you did not read the question, or if you did read it you did not understand it, or chose to ignore the actual question and answer a question not asked.
I appreciate all the time you put in on the group, but taking a subject offtopic is not appreciated.
Nowhere have I indicated that I am storing anything anywhere. Mike seemed to assume that I asked a different question as well.
To repeat:
Do you know the SQL for generating an amortization schedule, given the necessary values required for creating one, > USING SQL ONLY < ?
NO VBA, NO EXCEL FUNCTIONS, SQL ONLY
Thanks, Elaine
On Thu, 24 Mar 2005 00:59:33 GMT, "Larry Linson" <bo*****@localhost.not> wrote:
One of the basics of relational database design is that you do not store in a record any value that can be calculated whenever it is needed, from other values in that record. Retrieval time from rotating memory is so much slower than calculations that you need not worry about recalculations affecting performance.
Larry Linson Microsoft Access MVP
"Elaine" <no******************@commerce.state.ak.us> wrote in message news:f3********************************@4ax.com. ..
Mike,
Payment amount is *not* superfluous unless one wants to recalculate the payment amount for every row, which would be an awful lot of calculations.
~~~~~ Here is a VB code fragment that produces an amortization schedule from given parameters: ' establish beginning balance fBal = pv ' For i = 1 To fNPers fInt = fRatePer * fBal fPrinc = fPmt  fInt If fBal < fPrinc Then ' adjust final payment amount fPrinc = fBal End If Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc, "Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i) fBal = fBal  fPrinc Next i ~~~~~
And, no, I don't want a temporary table. I want to know whether it is possible to do this using SQL only, having the givens to procede from.
An ADO recordset would work swell, but, again, I am not interesting in a solution which is not SQL only.
Thanks for your input.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike Preston) wrote:
On Mon, 21 Mar 2005 14:15:08 0900, Elaine <no******************@commerce.state.ak.us> wrote:
>This has to do with selfjoins and includes a running balance problem. > >Is it possible to do this using SQL alone? > >Amortization schedule > >Givens: beginning balance, payment amount, # of periods, rate per >period
payment amount is superfluous. it can always be determined from the other three, but for now let's assume that it is available.
>schedule would consist of columns for balance, principal paid, >interest paid, payment number > >output calculation would be, effectively > >balance, payment  (rate per period * balance), rate per period * >balance, row number > >Can this be done in Access SQL?
Are you willing to have a temporary table available for seeding the rows? If so, let's presume that you want a recordset returned for a loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where temptable.payment_number < numberofperiods + 1
where: numberofperiods = 60 expr1 is the balance expr2 = payment_amount  rate_per_period * expr1 expr3 = payment_amount  expr2
So, this boils down to the determination of the balance at the beginning of the period.
The balance is a simple mathematical equation that is a variation on the formula I posted a couple of days ago.
Balance = payment_amount * ((1  (1+i)^n)/(i)) where:
i = rate_per_period n = payments left (this is determined by number_of_payments  payment_number
Depending on the version of access you are using, you can use a disconnected recordset to create the seeds, so you can avoid the temp table completely.
mike
>Payment amount is *not* superfluous unless one wants to recalculate the payment amount for every row, which would be an awful lot of calculations.
Which means you agree with me. See Larry's response. This forum sees
this line of thinking every so often and you will find almost
universal agreement that saving information which can be calculated is
generally not a good idea.
~~~~~ Here is a VB code fragment that produces an amortization schedule from given parameters: ' establish beginning balance fBal = pv ' For i = 1 To fNPers fInt = fRatePer * fBal fPrinc = fPmt  fInt If fBal < fPrinc Then ' adjust final payment amount fPrinc = fBal End If Debug.Print Format(fBal, "Currency") & Chr(9) & Format(fPrinc, "Currency") & Chr(9) & Format(fInt, "Currency") & Chr(9) & CStr(i) fBal = fBal  fPrinc Next i ~~~~~
And, no, I don't want a temporary table. I want to know whether it is possible to do this using SQL only, having the givens to procede from.
Well, SQL is intended, primarily, to operate on the basis of
recordsets. If you aren't willing to have a prepopulated recordset
(a temp table) and aren't willing to create a disconnected recordset
to operate from (ADO) then what were you referring to when you
mentioned that you thought the solution involved a selfjoin? Is the
information you mentioned available through memory or from a recordset
of some sort? Please describe what it is we have to work with.
An ADO recordset would work swell, but, again, I am not interesting in a solution which is not SQL only.
On Tue, 22 Mar 2005 00:08:58 GMT, mb******@pacbell.net.invalid (Mike Preston) wrote:
On Mon, 21 Mar 2005 14:15:08 0900, Elaine <no******************@commerce.state.ak.us> wrote:
This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
payment amount is superfluous. it can always be determined from the other three, but for now let's assume that it is available.
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Are you willing to have a temporary table available for seeding the rows? If so, let's presume that you want a recordset returned for a loan with 60 rows.
Select expr1, expr2, expr3, payment_number from temptable where temptable.payment_number < numberofperiods + 1
where: numberofperiods = 60 expr1 is the balance expr2 = payment_amount  rate_per_period * expr1 expr3 = payment_amount  expr2
So, this boils down to the determination of the balance at the beginning of the period.
The balance is a simple mathematical equation that is a variation on the formula I posted a couple of days ago.
Balance = payment_amount * ((1  (1+i)^n)/(i)) where:
i = rate_per_period n = payments left (this is determined by number_of_payments  payment_number
Depending on the version of access you are using, you can use a disconnected recordset to create the seeds, so you can avoid the temp table completely.
mike
Elaine wrote: This has to do with selfjoins and includes a running balance
problem. Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Thanks, Elaine
I think I understand the question :). I'll sketch what I did using
your variable names.
tblNatural
ID AutoNumber
1
2
3
....
1000
qryPeriods
SELECT (SELECT Count(*) FROM tblNatural AS A WHERE A.ID <
tblNatural.ID) + 1 AS thePeriod FROM tblNatural WHERE tblNatural.ID <=
fNPers;
gave:
1
2
3
....
fNPers
The formula for the Balance (can naturally go negative unless the final
payment condition is used if fNPers is too high):
Bal(thePeriod) = pv * (1 + fRatePer) ^ thePeriod  fPmt * ((1 +
fRatePer) ^ thePeriod  1) / fRatePer
qryAmortization
SELECT thePeriod, IIf(Bal(thePeriod) > 0, Bal(thePeriod), 0) AS Balance
FROM qry Periods;
That is, if the balance is positive, use it, otherwise the final
payment of Bal(thePeriod  1) * (1 + fRatePer) will send the balance to
0. So the 'real' qryAmortization has Bal(thePeriod) replaced with the
expression above and Bal(thePeriod  1) replaced with the expression
above using extra parentheses where needed around thePeriod  1
replacing thePeriod. The formula for the Balance was found by solving
the recurrence relation Bal(j) = Bal(j1) * (1 + fRatePer)  fPmt. The
principal paid and interest paid involves even more cut and paste and
IIF logic for the final values. Needless to say, I have not tested the
final result but I did try out the formula for the balance and it
seemed to work correctly. I also didn't see what would happen when a
zero exponent is used, but the values are correct if Access puts in a
one when that happens.
James A. Fortune
On 24 Mar 2005 20:25:10 0800, ji********@compumarc.com wrote: Elaine wrote: This has to do with selfjoins and includes a running balanceproblem. Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Thanks, Elaine
I think I understand the question :).
About as well as I did, I guess. ;)
Your tblNatural is what I suggested as the temporary table to seed the
rows needed. Your subselect statement is a good idea because it
allows the contents of the temp table to have anything at all (even be
used primarily for something else), have the autonumbers be generated
randomly or sequentially [even with gaps] and still return the proper
row seeds, as long as the number of rows in that table are sufficient
to allow all the rows in the amortization to be calculated. I had
envisioned something a bit more structured where the temp table has
one field in it and the field has a unique number in it representing
the number of the row. I suppose my sample sql statement should have
had an 'order by' clause to drive home that point. As it is, it would
return all the correct information, but the order might be less than
desirable. :(
I'll sketch what I did using your variable names.
tblNatural ID AutoNumber 1 2 3 ... 1000
qryPeriods SELECT (SELECT Count(*) FROM tblNatural AS A WHERE A.ID < tblNatural.ID) + 1 AS thePeriod FROM tblNatural WHERE tblNatural.ID <= fNPers;
gave: 1 2 3 ... fNPers
The formula for the Balance (can naturally go negative unless the final payment condition is used if fNPers is too high):
Bal(thePeriod) = pv * (1 + fRatePer) ^ thePeriod  fPmt * ((1 + fRatePer) ^ thePeriod  1) / fRatePer
I think you meant to use "beginning balance" where you put "pv".
You use two exponentiations, where mine only used one. We have
already determined that CPU cycles are inexpensive, so no point in
quibbling over those. But my formula is more efficient. ;) You
solve for balance using beginning balance, interest rate and period
number. I solve for balance using payment amount, interest and period
number.
Since we have already established that if you have three of these,
they uniquely define the fourth, it is just a matter of which is
available when that determines the best formula to use.
But they are identical solutions.
mike
qryAmortization SELECT thePeriod, IIf(Bal(thePeriod) > 0, Bal(thePeriod), 0) AS Balance FROM qry Periods;
That is, if the balance is positive, use it, otherwise the final payment of Bal(thePeriod  1) * (1 + fRatePer) will send the balance to 0. So the 'real' qryAmortization has Bal(thePeriod) replaced with the expression above and Bal(thePeriod  1) replaced with the expression above using extra parentheses where needed around thePeriod  1 replacing thePeriod. The formula for the Balance was found by solving the recurrence relation Bal(j) = Bal(j1) * (1 + fRatePer)  fPmt. The principal paid and interest paid involves even more cut and paste and IIF logic for the final values. Needless to say, I have not tested the final result but I did try out the formula for the balance and it seemed to work correctly. I also didn't see what would happen when a zero exponent is used, but the values are correct if Access puts in a one when that happens.
James A. Fortune
Mike Preston wrote: I think I understand the question :). About as well as I did, I guess. ;)
Your tblNatural is what I suggested as the temporary table to seed
the rows needed. Your subselect statement is a good idea because it allows the contents of the temp table to have anything at all (even
be used primarily for something else), have the autonumbers be generated randomly or sequentially [even with gaps] and still return the proper row seeds, as long as the number of rows in that table are sufficient to allow all the rows in the amortization to be calculated. I had envisioned something a bit more structured where the temp table has one field in it and the field has a unique number in it representing the number of the row. I suppose my sample sql statement should have had an 'order by' clause to drive home that point. As it is, it
would return all the correct information, but the order might be less than desirable. :(
Just so I don't get crucified on Good Friday :), but justifiably in my
case, qryPeriods can use a simple WHERE instead of the selfjoin. But
she did ask for one! I agree that your method works. I'll sketch what I did using your variable names.
tblNatural ID AutoNumber 1 2 3 ... 1000
qryPeriods SELECT (SELECT Count(*) FROM tblNatural AS A WHERE A.ID < tblNatural.ID) + 1 AS thePeriod FROM tblNatural WHERE tblNatural.ID
<=fNPers;
gave: 1 2 3 ... fNPers
The formula for the Balance (can naturally go negative unless the
finalpayment condition is used if fNPers is too high):
Bal(thePeriod) = pv * (1 + fRatePer) ^ thePeriod  fPmt * ((1 + fRatePer) ^ thePeriod  1) / fRatePer I think you meant to use "beginning balance" where you put "pv".
I looked at her sample VBA code and pv seemed to be the variable she
used for Beginning Balance. You use two exponentiations, where mine only used one. We have already determined that CPU cycles are inexpensive, so no point in quibbling over those. But my formula is more efficient. ;) You solve for balance using beginning balance, interest rate and period number. I solve for balance using payment amount, interest and
period number.
Since we have already established that if you have three of these, they uniquely define the fourth, it is just a matter of which is available when that determines the best formula to use.
But they are identical solutions.
mike
I like your explanation. Efficiency is important. She should
definitely look at what you did. Alternatively, to get the exact same
solution I could have done a little more algebra on the formula. We
actually used the variables in the same way but varied our method on
whether period number or balance took precedence. Since the period
numbers can be calculated in advance with a formula also, the
differences in our solutions are minor.
James A. Fortune
Thanks, Jim, and Mike, for your input.
My observations:
Cpu cyles are not cheap on a web server.
It is bad programming practice to repeat calculations in a loop.
The amortization object is separate from the calculation object, not
the least because of floating point effects.
Elaine
On 24 Mar 2005 20:25:10 0800, ji********@compumarc.com wrote: Elaine wrote: This has to do with selfjoins and includes a running balance problem. Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Thanks, Elaine
I think I understand the question :). I'll sketch what I did using your variable names.
tblNatural ID AutoNumber 1 2 3 ... 1000
qryPeriods SELECT (SELECT Count(*) FROM tblNatural AS A WHERE A.ID < tblNatural.ID) + 1 AS thePeriod FROM tblNatural WHERE tblNatural.ID <= fNPers;
gave: 1 2 3 ... fNPers
The formula for the Balance (can naturally go negative unless the final payment condition is used if fNPers is too high):
Bal(thePeriod) = pv * (1 + fRatePer) ^ thePeriod  fPmt * ((1 + fRatePer) ^ thePeriod  1) / fRatePer
qryAmortization SELECT thePeriod, IIf(Bal(thePeriod) > 0, Bal(thePeriod), 0) AS Balance FROM qry Periods;
That is, if the balance is positive, use it, otherwise the final payment of Bal(thePeriod  1) * (1 + fRatePer) will send the balance to 0. So the 'real' qryAmortization has Bal(thePeriod) replaced with the expression above and Bal(thePeriod  1) replaced with the expression above using extra parentheses where needed around thePeriod  1 replacing thePeriod. The formula for the Balance was found by solving the recurrence relation Bal(j) = Bal(j1) * (1 + fRatePer)  fPmt. The principal paid and interest paid involves even more cut and paste and IIF logic for the final values. Needless to say, I have not tested the final result but I did try out the formula for the balance and it seemed to work correctly. I also didn't see what would happen when a zero exponent is used, but the values are correct if Access puts in a one when that happens.
James A. Fortune
Elaine wrote: Thanks, Jim, and Mike, for your input.
My observations: Cpu cyles are not cheap on a web server.
It is bad programming practice to repeat calculations in a loop.
Ouch, those nails hurt :). One way to get around this is to get the
balance in the second query and use it for calculations in a third
query instead of repeating the calculation. Good observation. It
looks like there is still plenty of room for optimization.
James A. Fortune
One of my ancestors was burned to death in Salem falsely accused of
being a witch.
Elaine <no******************@commerce.state.ak.us> wrote in
news:mt********************************@4ax.com: This has to do with selfjoins and includes a running balance problem.
Is it possible to do this using SQL alone?
Amortization schedule  Givens: beginning balance, payment amount, # of periods, rate per period
schedule would consist of columns for balance, principal paid, interest paid, payment number
output calculation would be, effectively
balance, payment  (rate per period * balance), rate per period * balance, row number
Can this be done in Access SQL?
Thanks, Elaine
Yes. You need to look up in a Financials book what the formulas for
figuring out the Interest and Principal for a given payment based on the
period number.
Then, you make a table that just has payment numbers in it (if you're
doing a Mortgage table, it's going to go out at least *360* rows.). Then,
your query just queries against that table sort of like this:
parameters [numperiods] as LONG INTEGER, [startamt] as currency, [rate]
as double;
select period, [numperiods],[rate],[startamount], GetInterest(period,
[numperiods], [startamt], [rate]), GetPrinciple(period, numperiods,
startamt, [rate])
from Periods
where period < numperiods
There is probably also a similar function to the payment functions that
can generate your period total payment amount as well.
Lucky for mortgage payments, the maths support this so you don't have to
do a running sum.
Otherwise, what you have to do is a selfjoin in order to do it purely in
SQL. Not a big deal in Access for a 30yr mortgage, even, unless you were
doing it over a few thousand accounts or with your data on a painfully
slow network server in a bloated MDB file.
Otherwise, do it in a report, like JNikle suggested. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Gary 
last post: by

198 posts
views
Thread by Sy Borg 
last post: by

6 posts
views
Thread by Kennedy_f 
last post: by

9 posts
views
Thread by denis 
last post: by
  
16 posts
views
Thread by Mr Shore 
last post: by

12 posts
views
Thread by Mr Shore 
last post: by

4 posts
views
Thread by Jim Rutledge 
last post: by
          