Amortization Query

[Swatkins]

Good afternoon,

I'm working on a database where I enter a series of assets and their costs, and I'm trying to build a query that will generate an amortization schedule for each asset.

I'm using an amort methodology that amortizes a certain amount each period based on a calculation. I've got a query that generates the amount that would be amortized based on that calculation.

My problem is in the last period of amortization before the asset is fully amortized. In that last period, the amort methodology may generate an amount to be amortized that would be greater than the total value of the asset. In such cases, I want the amort for that period to be the total value of the asset less the cumulative amort up to this period.

So, for example, I may have an asset that is worth $225. My amort calculation generates an amort amount of $50 per period. For the first four periods, the amort would be $50. But in the final period, only $25 would remain unamortized, and in that instance I want the final amort amount to be $25 instead of $50.

Structurally, I've already got a query with the following fields:

[AssetName], [MonthStartDate] (i.e. the first day of the month for the period of amortization), [BusUnit], [RawMonthAmort]. [RawMonthAmort] has an expression that performs the calculation that determines how much to amortize. I've tested this part and it all works (except for during the final month of amortization, as noted above).

I tried including another layer of conditionals in the [RawMonthAmort] expression that would calculate a cumulative total amort and check that against the total asset value (by bus. unit) and give the difference if the cumulative total amort exceeded the asset value, but I couldn't figure out how to make that work.

Any suggestions? Anything I can provide that would clarify the problem?

Thanks!

Stephen

 

[Pat Hartman]

You're going to need VBA to do this. The following is an example of creating an amortization schedule for a mortgage. Your situation is similar and so you should be able to use similar logic.

Public Sub WriteRecs(StartDate As Date)

    Dim Mths As Integer
    Dim PmtNum As Integer
    Dim BeginningBal As Currency
    Dim EndingBal As Currency
    Dim PmtDate As Date
    Dim CumInt As Currency
    Dim SchedPmt As Currency
    Dim IntRate As Double
    Dim PmtsPerYear As Integer
    Dim MtgAmt As Currency
    Dim MtgTerm As Integer
    Dim PmtYear As Integer
    Dim PmtMonth As Integer

On Error GoTo Exit_Proc

    Set td = db.TableDefs!tblAmortization
    Set rs = td.OpenRecordset
    
    Mths = rsMtg!MortgageTermYears * 12
    BeginningBal = rsMtg!MortgageAmt
    PmtDate = StartDate
    CumInt = 0
    IntRate = rsMtg!InterestRate
    PmtsPerYear = Nz(rsMtg!PmtsPerYear, 12)
    MtgAmt = rsMtg!MortgageAmt
    MtgTerm = rsMtg!MortgageTermYears
    SchedPmt = -Pmt(IntRate / PmtsPerYear, MtgTerm * PmtsPerYear, MtgAmt, 0, 0)
    PmtYear = 1
    PmtMonth = 1
    
    PmtNum = 1
    Do Until PmtNum > Mths
        rs.AddNew
        rs!MortgageID = rsMtg!MortgageID
        rs!PmtNum = PmtNum
        rs!PmtYear = PmtYear
        rs!PmtMonth = PmtMonth
        rs!PmtDate = PmtDate
        rs!BeginningBal = BeginningBal
        rs!SchedPmt = SchedPmt
        rs!ExtraPmt = Nz(rsMtg!ExtraPmt, 0)
        rs!TotPmt = SchedPmt + rs!ExtraPmt
        rs!Interest = BeginningBal * (IntRate / PmtsPerYear)
        rs!Principal = rs!TotPmt - rs!Interest
        rs!EndingBal = BeginningBal - rs!Principal
        CumInt = CumInt + rs!Interest
        rs!CumInterest = CumInt
        BeginningBal = rs!EndingBal
        PmtNum = PmtNum + 1
        PmtDate = DateAdd("m", 1, PmtDate)
        rs.Update
        PmtMonth = PmtMonth + 1
        If PmtMonth > 12 Then
            PmtYear = PmtYear + 1
            PmtMonth = 1
        End If
    Loop
    
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select
End Sub

 

[Swatkins]

Thanks.

I'm cool with doing this in VBA (just hadn't thought to do that).

I'll have to give this a look-over to make sure I can parse it and reproduce something similar.

 

[Swatkins]

Hey, a couple questions on this:

In the code above, I don't see where you Dim td, db, rs or rsMtg. Is that normal? (My first thought was: wouldn't that generate an error?)

tblAmortization is a table that already exists within the database, with the structure already defined, right? (Or, in other words, the VBA isn't acting like a MakeTable query but more like an Append or Update Query, right? I realize that it's not actually a query in the technical sense, but functionally that's sort of what we're doing here, right?)

Thanks again!

 

[Pat Hartman]

The code was taken from a larger app so there may be some missing parts. In the case of td, db, etc. they are objects that are used by other procedures so they are defined as public variables in another module.

The code isn't creating the amortization table. It is adding rows to it. One row for each month of the amortization period. The startdate is passed as an argument and the term is obtained from an open recordset. The calling procedure deletes the amortization generated previously for the property and then calls the procedure I posted once for each mortgage associated with the property to generate new amortization schedules.

 

[Swatkins]

Got it. I had a feeling they must've been declared in some other public module, but wanted to make sure I wasn't missing something.

I'm not sure yet whether I want to erase Amort Data from the table when generating new amort data or retain previously calculated data, but for now it doesn't matter because I'm mostly testing the feasibility of this approach. (Previously everything was done in Excel, and it's a fine implementation, but lousy for storing data long-term.)

I'll continue plugging away at this. Thanks!

 

[Pat Hartman]

I would suggest deleting it. To leave it would simply lead to confusion. It's not like this is user entered data.

 

[Swatkins]

Yeah, I'd agree. If I do decide to keep the old amort data, it'll be tagged with some sort of unique versioning identifier that I can use to query on later. That would be purely for the purpose of "what was the amort for the prior version of this asset acquisition?" But since I'm still in very early design stages I'm not sure if that will actually be useful or not. Deleting the old data would make generating the table more like running a query each time, which is how I'd originally wanted to do it, so I think it's more likely I'll do that.