Hello Awesome AIrtablers people. I am trying to transfer a formula from our Budget tracker Excel to an Airtable new base, It’s a bit tricky. We are a VOD online service and they are trying to see how the content is being consumed and amortized through the years, this has some rules
1- So they have set some rules when it comes to amortization percentage through a logic they already have, This might change according to some research, that’s why they have it in a separate tab inside excel and it’s synced inside the excel through a lookup formula, so if they change the percentage values will change in the main table: look at this image:
2- so according to these percentage: they follow and calculate the amount to be amortized each year according to each percentage
so let’s say the total cost of Title (A): is 5000,000M, the release date is : 1/1/2021
1st Year amortization is: 100% so 5M will be amortized in 12 months and each month will have the amount 5M/12: so it’s : 416666.66 for each month and for the remaining 6 years is 0
Something like the below: they formulated the amount of amortization each year ( 6 years ) according to each percent in the first table
3- Each year is split into 12 months, they just added the months manually and each month takes the amount from the assigned year and it goes automatically: look at this image:
Can I get some help to convert that inside Airtable through some Lookups and rollups formula to calculate that each month for all titles according to the cost and release date and amortization percentage they added?
Thanks for your time guys.
