tough SQL question

[Elaine]

This has to do with self-joins 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

[pietlinden]

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.

[Mike Preston]

On Mon, 21 Mar 2005 14:15:08 -0900, Elaine
<no******************@commerce.state.ak.us> wrote:

This has to do with self-joins 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]

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 self-joins 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 Linson]

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 self-joins 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]

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 off-topic 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 self-joins 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
>

[MGFoster]

-----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 <decimal-point> 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 off-topic 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 self-joins 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 Preston]

>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 pre-populated 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 self-join? 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 self-joins 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

[jimfortune]

Elaine wrote:

This has to do with self-joins 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(j-1) * (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]

On 24 Mar 2005 20:25:10 -0800, ji********@compumarc.com wrote:

Elaine wrote:

This has to do with self-joins 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(j-1) * (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

[jimfortune]

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 self-join. 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

[Elaine]

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 self-joins 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(j-1) * (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

[jimfortune]

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.

[corey lawson]

Elaine <no******************@commerce.state.ak.us> wrote in
news:mt********************************@4ax.com:

This has to do with self-joins 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 self-join in order to do it purely in
SQL. Not a big deal in Access for a 30-yr 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.