Solved

Amortization based on start/end dates

  • 18 April 2024
  • 6 replies
  • 150 views
Y
Rank
Userlevel 3
Badge +6
  • Yaejis Newly Minted Pigmenteer
  • 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

francois
Rank
Userlevel 6
Badge +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

François (Pigment)
Y
Rank
Userlevel 3
Badge +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']

 

francois
Rank
Userlevel 6
Badge +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']
François (Pigment)
Y
Rank
Userlevel 3
Badge +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!!

 

Igor P
Rank
Userlevel 2
Badge +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!

Igor Poulis | Solutions Architect & Expert Modeler @ Pigment
Y
Rank
Userlevel 3
Badge +6

Thanks for sharing @Igor P !

Reply


Ask your question here!

Terms & ConditionsCookie settings