1

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:

What I have now

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)

5
  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jan 13 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? – Erik Tyler Jan 13 at 19:22
  • @marikamitsos thank you, I've update the post with my shared sheet. – simen Jan 14 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. – simen Jan 14 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. – marikamitsos Jan 14 at 17:22
2

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

In Google Sheets, your B13 formula would then look like this:

=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).

enter image description here

[Image courtesy of @marikamitsos]

5
  • Okay, thats it, I'm siging up for an adult math class now. Thank you so much Erik! – simen Jan 14 at 12:08
  • For better understanding of what the formula does and how the data is structured please see this screenshot – marikamitsos Jan 14 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.) – Erik Tyler Jan 14 at 19:40
  • Thanks for adding the more permanent record, @marikamitsos. – Erik Tyler Jan 14 at 19:40
  • Sure. Feel free and add it within your answer. Not many users make it to the comments. – marikamitsos Jan 14 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.