# Calculating a total value based on X periods, that takes the previous period into account, without intermediate calculations?

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?

Here's the sheet I'm working on(editable)

• Welcome. Please read how to and share a test sheet so as you can be easier helped. Commented Jan 13, 2021 at 18:46
• Your summed amount in E10 shows \$5203.44. Yet your amount in red, which you say you want in B13, is \$5,489.11. How did you arrive at the red number from the sum in E10? Commented Jan 13, 2021 at 19:22
• @marikamitsos thank you, I've update the post with my shared sheet. Commented Jan 14, 2021 at 6:02
• @ErikTyler I messed up. I removed some years to make it easier to read, but forgot to update the total. I've updated the screenshot now. The more year I add the lower the sum will be, because of the discount rate and retention rate. Commented Jan 14, 2021 at 6:03
• Sorry for the late reply. I see though that an elegant solution is provided to you. Kudos @ErikTyler. Please remember that you can also upvote the answer so others can benefit as well. Commented Jan 14, 2021 at 17:22

This comes down to all those algebra classes no one thought they'd ever need in real life. I can't teach algebra here, but the basics:

X = base year (from which all other years follow, i.e., your B6 amount)

Let B = your discount. Rather than thinking of it as what is taken away, however, we should think of B as what remains. This leaves us with XB (which, in your example case, is X times .92 or 92%). In effect then this is 1-your B7 amount.

Let A = your retention rate.

We then arrive at XA-XB, which we can rewrite as X(A-B).

X is a constant now, and the A-B portion will apply every year for each of Z years, i.e., for four years: X(A-B)z1 + X(A-B)z2 + X(A-B)z3 + X(A-B)z4 [where Z1, Z2, etc. are an exponent for each year].

`=ArrayFormula(SUM(ROUND(B6*(B8*(1-B9))^(B11-SEQUENCE(B11,1)),2)))`

The `SEQUENCE` will form the exponents (^) for as many years as requested, and `SUM` will sum that list.

`ROUND( ... ,2)` will round each of the exponential years to two decimal places (i.e., standard dollars-and-cents).

[Image courtesy of @marikamitsos]

• Okay, thats it, I'm siging up for an adult math class now. Thank you so much Erik! Commented Jan 14, 2021 at 12:08
• For better understanding of what the formula does and how the data is structured please see this screenshot Commented Jan 14, 2021 at 17:32
• But I bet you had more fun and people thought you were cooler in high school, Simen. (It's not until you're an adult that people think the nerds are cool.) Commented Jan 14, 2021 at 19:40
• Thanks for adding the more permanent record, @marikamitsos. Commented Jan 14, 2021 at 19:40
• Sure. Feel free and add it within your answer. Not many users make it to the comments. Commented Jan 14, 2021 at 21:44