Table Structure and Calculations Question

[Dave87m]

Looking for some advice on how to set the below up. Im new to Access and working through some courses but this seems more like a structure/experience question.

Working with loans that have a tiered interest structure based on the time from loan origination. The interest for the loans is setup in a tier structure based on length of time between the Loan Origination Date and the Settlement date typically in 6-month increments. For example: a loan amount of $1000 on given on 1/1/2022. Pay structure may be: 0-6 months – 15%; 7-12months 20%; 13-18months 25%; and so on. If paid back on 9/1/2022, then total return is $1,200 because the payoff fell within the second interest tier.



The challenge is the interest tier structure may vary (typically by % and not time increment). Im trying to build calculations that calculate on a form what the payoff amount should be based on the associated tier structure. My first thought was a Loan Table with dates and amounts and then an interest table with records being the interest percentages in fields that are each six month increment. But Im not sure how to work the math into this to select which interest tier is applicable based on the current time or settlement time from the loan origination date. Or if this two table system is even the best way to do this. Any suggestions?

[CJ_London]

what happens if a partial repayment is made? Or is it a case that any loan can only be settled in full in one payment?

And from a borrowers perspective, a loan made on 1/1/2020 will have the same repayment amount whether the loan is repaid on 1/2/2022 or 6/30/2022? i.e. the interest is not compound

for tables perhaps something like this

tblRateTypes
RateTypePK...RateTypeName...TierPeriod
1.................Standard............6
2.................MatesRates.........9

tblRateTiers
PK..RateTypeFK..Period...Pcent
1..........1...........1.........15
2..........1...........2.........20
3..........1...........3.........25
4..........1...........4.........30
5..........1...........5.........35
6..........2...........1.........5
7..........2...........2.........10
8..........2...........3.........15
9..........2...........4.........20

period relates to the period when the repayment is made so period 1 is first x months defined by TierPeriod (which perhaps defaults to 6)

you can determine the period using the datediff function

(datediff("m",loanMadeDate,DateRepaid) \ TierPeriod)+1

[Dave87m]

Thank you Ajax! In this case the loan will only be settled in one lump sum payment at the end - not partial settlements throughout which helps keep things simple. Its possible for partial payments at the end but Im not too worried about that because it doesnt change the calculation of what the payment should be. Also interest is not compounded, just a simple interest calculation based on the months and tiers.

Your method above is very helpful. I didnt think to split the rate types and the rate tiers into two separate tables. Ive been working with that method this morning and Im very close but have one last question.

Using your ideas above, for each record I can determine the months that have elapsed and what Tier the record should be in. But now I need to associate that tier and RateTypePK with the percentage in the tblRateTiers. Ive been attempting to do that with a dlookup, but Im struggling with the criteria that involves both the calculated Tier above, and the RateTypeFK. My failed attempt is below. Im not sure if I should be concatenating/lookup the two together (what I might do in excel) or using AND logic to include both those criteria. Thanks!

[ssanfu]

This is what you have as the PercentLookup control source

=DLookUp("percent","RateTiersT","RateTypeID = " & [RateTypeID] AND "Tiers=" & [RateTiersT![Period])

Two issues that I see:

1) [RateTiersT![Period] You CANNOT refer to a field in a table like this. In any case you should be referring to the Tiers text box control on the form.

2) The syntax - you are missing an ampersand and a double quote after [RateTypeID] in the middle of the formula

For the PercentLookup control, try

=DLookUp("percent","RateTiersT","RateTypeID = " & [RateTypeID] & " AND "Tiers = " & [Tiers])

[CJ_London]

Am away from my computer for a few days, so will need to leave for others to respond. Looks like Steve has a handle on it