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

 

[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!

 

[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!

 

[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.