472,333 Members | 1,075 Online

# tough SQL question

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
Nov 13 '05 #1
13 2070 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.

Nov 13 '05 #2
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

Nov 13 '05 #3
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.

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

Nov 13 '05 #4
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.

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

Nov 13 '05 #5
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

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.

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
>

Nov 13 '05 #6
-----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.
--
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

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.

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

Nov 13 '05 #7
>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

Nov 13 '05 #8
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

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

Nov 13 '05 #9
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

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

Nov 13 '05 #10
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
I'll sketch what I did using

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

Nov 13 '05 #11
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

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

Nov 13 '05 #12
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.

Nov 13 '05 #13
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.

Nov 13 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 1 by: Gary | last post by: Hello guys! Bear with me,I am newbie. I am trying to limit the privileges of the user PUBLIC has as toughly as possible which means I want to... 198 by: Sy Borg | last post by: Hello: We are designing two multi-user client server applications that performs large number of transactions on database servers. On an average... 6 by: Kennedy_f | last post by: I did better in terms of score on this one than 291, but I found it much harder. Wordings of questions are difficult like the rest, but the DNS and... 9 by: denis | last post by: Hi there, I got a tough interview questions lately, and I would like to hear your opinion: An array of N chars is given Write an efficient... 1 by: vignesh19 | last post by: Hi, I have to develop a web app for registration of persons for a particular training. Before entering into the registration form, a login... 0 by: asterisk | last post by: Hi, I am already mad for charset issue, please help me clear it. My question here is, I want to retrieve fields from Sql server database, whose... 16 by: Mr Shore | last post by: hi all what I want to do is as below start a function halt on, until some event occurs(maybe user click) and then return some value accordingly.... 12 by: Mr Shore | last post by: how to do the follows in IE: there's page A with element a pop a new page B from script in A and append element a into B after closing page B... 4 by: Jim Rutledge | last post by: ok ok , anyone know anything on this tough question? How do you determine the length in seconds that a midi file is , or any audio file for that... 0 by: teenabhardwaj | last post by: How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of... 0 by: Kemmylinns12 | last post by: Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and... 0 by: CD Tom | last post by: This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right... 0 by: CD Tom | last post by: This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins... 0 by: jalbright99669 | last post by: Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made... 0 by: antdb | last post by: Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was... 2 by: Matthew3360 | last post by: Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it... 0 by: Arjunsri | last post by: I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and... 0 by: WisdomUfot | last post by: It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...