Excel Formula to Calculate Payback Period

[acool]

Hi Everyone,

I am currently trying to create an excel formula which calculates the Payback Period (Months) based on a a Total Purchase Amount of $4.8M using the inputs A7:C7. While I would normally use a sum product to multiply monthly unit sales by Contribution margin divide that by the total purchase amount to get to the payback period , this differs in the fact that their is a limited number of months on hand for each of the products. For Example, Product #1 only has 8 months of units on hand, and can therefore only have contribution margin dollars for 8 Months. I have exemplified this idea in the table below which breaks down at which month CM for each of the products. I am curious if there is a way to incorporate this idea into a simplified excel formula. Based on the more manual table created below, the payback period should be 10 months. Any help would be greatly appreciated. Thank You!

 

[StephenCrump]

Duplicated here: https://www.mrexcel.com/board/threads/excel-formula-to-calculate-payback-period.1250466/

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.