I'm trying to create a simple Customer Lifetime Value calculation in Google Sheets that takes 4 variables:
- Income first year
- Retention rate (% chance of being a customer the year after)
- Discount rate (% discounted the value per year, after retention rate is taking into account)
- Amount of years
I've done this successfully by doing the calculations per year manually in separate columns. Like this:
For calculating the years I use the following formula:
(Previous year * Retention rate) - ((Previous year * Retention rate) * Discount rate)
But is there any way to find the same total sum, without having to do intermediate calculations for each year manually in separate columns?
My goal is to type in the amount of years and get the same result, without all the stuff in the black circle in the screenshot.
I guess I could write a loop using the Script editor, to iterate the formula by passing the variables that way. But is there any other way?