How do I model a "variable appreciation calculator"?

How do I?
1

Hi guys,

I feel like drawing a mental blank here :wink: can you help me out ?

Let’s say we purchase an asset in several chunks, and want to activate it and appreciate/amortize it across a certain period.

To keep things simple we assume every chunk gets appreciated/amortized linearly (i.e. equal appreciation amounts each month) over the course of 36 months.

To keep the model tidy, I don’t want to add a separate variable for each chunk, but only enter the invoiced amounts in each month in a single variable.

From that I can then calculate both cashflow (payment terms) and … at least I hope … appreciation/amortization.

So I’m looking for a formula that basically does the following:

  • for a period p1, check the value in the “invoiced amount” variable
  • take that invoiced amount in p1 and distribute it evenly across 36 months, i.e. p1 thru p36.
  • do the same for the next period, taking the invoiced amount in p2 and distribute it evenly across 36 months, i.e. p2 thru p37
  • sum up all the distribution amounts for each period to land on the total appreciation/amortization amounts per period.

Any idea ?

I’ve been playing around with ramp_normalized & Co but without success. It doesn’t feel to be too “rocket-sciency” though

Thank you & Best Regards
Fabian

2

Hey Fabian, that’s a perfect use-case for the spread function. Check out our forum post about it here:
How do I use the ‘spread’ function in Causal?

In your example it would look like this:

image
image1688×364 61.1 KB

image
image1954×366 61.6 KB

3

aah that’s great, thank you @lorenz_causal !!

I actually just came to an alternative solution just now … brutally simple so it’s really been a mental blank:

sum(InvoicedAmount[t - 36...t])/36

That also seems to do the trick. Or am I overlooking something?

4

Yup, that’s also a great solution!

The spread function is a bit more flexible, because it also works if you are not distributing equal amounts every month (e.g. 3%/month in the first year, and 2%/month in the second year). Then the simple sum approach wouldn’t work.

1 Like