Skip to main content

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!

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

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' )sBY:'List_Associate ID']

 

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']

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

 

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!

Thanks for sharing @Igor P !

Reply


Ask your question here!

Terms & ConditionsCookie settings