balance and allocation formulas

[elfudge35]

I have a database that has the following components

item number, beginning balance, proceeds (sale and additional), proceed allocation (between principal and outstanding charges), charges (interest and legal expenses), total outstanding charges, ending balance

there are many items that refer to different loans

the proceeds are allocated first to the outstanding charges, and if there are proceeds left over, to the principal

I want to make this a query in which all columns but the proceeds and charges are calculated automatically, the beginning balance can be pulled from a parent table based on the item number

I am used to excel and have virtually no experience with access function and am completely lost trying to do this

any help would be great

thanks

 

[elfudge35]

anyone?

 

[lespaul]

it would be more helpful if you describe the table(s), any relationships between the tables if any, some data and your expected results:

TableName
FieldName1 FieldName2 etc.
Data data etc.
data data

Expected Results
FieldName2 FieldName6 etc.
Data data etc.
data data


thanks,
leslie


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[elfudge35]

the parent table has one line for each item (loan) and the default balance of the loan

the child table has numerous dates for each item in which transactions affect the balance, 4 columns of essential data and 5 columns of data based on those

essential
Sale Proceeds Additional Proceeds Interest Expense Legal Expense

columns that need to be calculated
Beginning Balance (the default balance if it is the first date for the item, previous ending balance otherwise)
ALLOCATION COLUMNS
Principal Balance (the amound of the proceeds to be allocated to the balance of the loan)
Outstanding Charges (the remaining amount of the proceeds to be allocated to the outstanding charges)
TOTAL COLUMNS
Total Outstanding Charges (running total of the charges not allocated to the principal)
Ending Balance (Beginning Balance minus proceeds allocated in Principal Balance)

 

[lespaul]

again, we would like to "see" what you mean.

ParentTable
LoanNumber DefaultBalance
############ $##########
############ $##########

ChildTable
LoanNumber(I guess?) SaleProceeds AddProc Interest
##############

Results
LoanNumber* Begin Bal PrincBal** OutstandChg**
(data from above examples filled in here)


*(although you didn't say this I'm assuming you are looking for this information for each loan and not a total of all loans over a period of time)
**Formula for getting this calculation

you will find if you present your information in this manner someone will try to immediately post a solution for you. However, if we spend a lot of time guessing at what you want it gets very frustrating.

For an example, see Thread701-676215

 

[elfudge35]

[tt]Parent
Item Def Bal
1 10000
2 20000
3 15000
4 12000

Child
Item Date SaleProc AdPro IntExp LegExp
1 1/20 3000 0 0 0
1 2/15 0 100 20 50
2 1/15 0 0 12 0
2 3/4 15000 0 18 0
3 1/31 0 200 0 0
3 2/17 11000 0 40 50
4 1/1 6000 50 30 0
4 2/1 0 75 30 40

Results
Item Date BegBal SaleProc AdPro OutCh PriBal IntExp LegExp TotCh EndBal
1 1/20 10000 3000 0 0 3000 0 0 0 3000
1 2/15 3000 0 100 0 100 20 50 70 2900
2 1/15 20000 0 0 0 0 50 0 50 20000
2 3/4 20000 15000 0 50 14950 60 0 60 5050
3 1/31 15000 0 200 0 200 0 0 0 14800
3 2/17 14800 11000 0 0 11000 40 50 90 3800
4 1/1 12000 6000 50 0 6050 30 0 30 5050
4 2/1 5050 0 75 30 45 30 40 70 5005
[/tt]

 

[lespaul]

item #1, first record:

If the beginning bal is 10000, how is the ending bal 3000?
Where does PriBal come from?

item #1, second record:
Where does PriBal come from?
I'm guessing TotCh is IntExp + LegExp <- a formula???

item #2, second record:
where does the 50 OutCh come from?

I'm sorry that I can't read your mind.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[elfudge35]

sorry about the typo, should be 7000 ending balance

and the definitions for the OutstandingCharges, PrincipleBalance are in my previous post

Outstanding Charges is the amount of the proceeds that can be allocated to the previous Total Outstanding Charges balance in the second to last column
Then all remaining proceeds not allocated to TOC is allocated to the Principle Balance of the loan, which is then subtracted to get the Ending Balance

so, look at item 2, there are no proceeds in the first date, all of the expenses are totaled to give the total outstanding charges
in the second date there is a sale of 15000, so 50 is allocated to the outstanding charges because there is a balance of 50, the remaining 14950 is allocated to the balance of the loan and you get an ending balance of 5050
then there is an additional expense, so total outstanding charges becomes that amount

 

[MichaelRed]

... so, with some var in ternimology, thjis is just another running sum calculation for the ultra traditional loan balance calculation ... which i believe has been exhaustively conered here, there and elsewhere ...

so just do what ever search you find cnvenient and translate the field names as ye may.




MichaelRed