Lifetime value of a contract

Hello. I have a spreadsheet with contracts started in year 1, in year 2 x% continue on, in year 3 x% of the remaining contracts in year 2 stay, and so on and so on. I'm having difficulty coming up with the average lifetime of the 1000 contracts started in year 1. If anyone can help it would be appreciated. Thanks. Spreadsheet attached.

E1 = "Contracts Discharged"
E2 = C2 - C3
pull down to E29

H2 =SUM(E2:E29)
Total Contracts Discharged in Period
(check: is this equal to total starting number of contracts? Yes => none extend past check period)

H3 =SUMPRODUCT(A2:A29,E2:E29)/H2
Weighted Average Lifespan of Contracts in Period

Multiply how many contracts are discharged in a period by what year they're discharged in and sum up to get the total number of contract-years; then divide by number of contracts to get the weigthed average.

By the way, the result I'm seeing there is 2.1815 years.

Can you re-attach the spreadsheet with your calculation. You have nothing in H2. Also, 2.1815 years seems rather small, no?

Originally Posted by ammartino44

Can you re-attach the spreadsheet with your calculation.

Why?

Originally Posted by ammartino44

You have nothing in H2.

Originally Posted by ben_hensel

H2 =SUM(E2:E29)

Originally Posted by ammartino44

Also, 2.1815 years seems rather small, no?

Over 75% of your contracts ended in one year, so I thought it looked intuitively correct.

Why did I ask you to reupload the spreadsheet with your calculations? Well, you have this formula H3 =SUMPRODUCT(A2:A29,E2:E29)/H2 yet there is no value in H2 in the spreadsheet that I attached. Yeah, 75% of the contracts end after one year, but the remaining % have a very high life span (80+% renew).

Alright, I'm done.

LOL. New Yorkers. Sigh.

Dood, if you won't read the whole post, then there's literally nothing I can do. It doesn't matter what I do when you don't read it.

Ah my apologies New York You are correct!! Is there any other way of doing this? The discharged method isn't very intuitive imo.