Solved

# Amortization based on start/end dates

• 6 replies
• 158 views

Userlevel 3
+6
• Newly Minted Pigmenteer
• 21 replies

Hi Pigment team!

I am trying to setup a amortization schedule as following. Can you please help?

• Orange is given resources
• Green is the output that I would like to have in the table/metric
• Blue is just validation for myself which aligns with the orange Total column and doesn’t need to be in the model

Following is the formula I used in excel:

• For 1/31/2023 Column alone, formula is
``=(\$V5/\$AS5)*((AV\$4-\$AQ5)+1)``
• For 2/28/2023 and onwards, formula is
``=IF(EOMONTH(\$AR264,0)<AW\$4,0,IF(AW\$4<=\$AR264,(\$V264/\$AS264)*(TEXT(AW\$4,"d")),(\$V264/\$AS264)))``

I tried using the following formula in playground for 1/31/2023 column, but it doesn’t seem to work.

``IF(Month.'Start Date'=DATE(2023,01,1),('RSU Accounting Data'.'Expense Amortized Future'/'RSU Accounting Data'.'Amortization # of Days') * DAY(Month.'End Date'))``

Thanks for helping out!

icon

Best answer by francois 18 April 2024, 09:58

View original

### 6 replies

Userlevel 6
+14

Hi!
How about using the PRORATA function, multiplying by the amount and dividing by the number of days?

Something like `PRORATA(Month, 'RSU Accounting Data'.'Amortization Start Date', 'RSU Accounting Data'.'Amortization End Date') * 'RSU Accounting Data'.'Expense Amortized Future' / 'RSU Accounting Data'.'Amortization # of Days'` ?

Let me know how that works out for you

Userlevel 3
+6

Hi @francois , thanks for stepping in.

Yes Prorata sounds right! I tried as following.

I added By modifier at the end, because individual employees have different Amortization Start/End dates, Expense Amortized Future amount and Amortization # of Days. It seems the output is same for all employees and the amount for each month is too high. Do you have any advises? Thanks

``(PRORATA(Month, 'RSU Accounting Data'.'Amortization Start Date', 'RSU Accounting Data'.'Vesting Date') *'RSU Accounting Data'.'Expense Amortized Future' / 'RSU Accounting Data'.'Amortization # of Days' )[BY:'List_Associate ID']``

Userlevel 6
+14

The BY is probably not what you meant here - it looks like it should be something like that:

``[BY:'RSU Accounting Data'.'List_Associate ID']``
Userlevel 3
+6

That worked!

I noticed the result for each month is price per day so I added * DAYSINPERIOD(Month) at the end and got much closer to what I wanted.

Thanks!!

Userlevel 2
+3

Hello Yaejis!

I know this one has been marked as solved but, just in case, we also have the SPREAD function which is a classic one that we use within the CapEx use-case for the Depreciation/Amortization schedules.

I hope it can give you some further inspiration for future similar cases.

Thanks!

Userlevel 3
+6

Thanks for sharing @Igor P !