• 100% Satisfaction Guarantee
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6410
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now

# I need to create a formula in excel that will calculate income

I need to create a formula in excel that will calculate income over time. We have an insurance product that we receive an initial income on sign up then a recurring monthly income. Stats show that there is a 30% drop off on the policy monthly. An example is as follows:
1st Premium is \$ 579.00 Commission is \$ 289.50 the cost of signing the client is \$ 356.00 so we have a shortfall of -\$ 66.50 on the first premium. We will sign on 40 new members per month. Our annuity revenue is \$28.95 / policy per month. As said the drop off rate would be 30% per month.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Can you send me the sample Excel file with dummy data so that I can work on the formula directly with your dummy data? If this is possible, you can attach the Excel file here when you reply, or you can upload it in the link below:

http://wikisend.com

Best regards,
Jess
Customer: replied 3 years ago.
Andrew,

Thank you for writing back with the link. I got the file. Where exactly you want the formulas and what you want to calculate? Is it in the blue cells?

Jess
Customer: replied 3 years ago.

Yes the blue cells. You will see my formula are in the blue cells. The data runs along those cells.

Andrew,

Jess
Customer: replied 3 years ago.

OK

Andrew,

So you are saying that the current formulas you have in the blue cells are incorrect? Im tracing the formulas to see what you really want to compute there.

Jess
Customer: replied 3 years ago.

I need to confirm them because Im getting negative values. are they correct?

Andrew,

I am understanding it. So you are calculating income with the conditions you described. Please give me a moment to try various figures and other approaches to calculate the income. I will get back to you shortly.

Jess
Customer: replied 3 years ago.

thanks

Hi Andrew,

After studying your scenario for some time, I believe that the formulas that you are using are correct. However, the focal point on the correctness of your calculation is derived on HOW you calculate the 30% drop off.

Imagine, 30% drop off monthly can mean that EVERY month, 30% of the CURRENT figure (sales or premium paid) is lost because the policy holder discontinues. So, if this is the correct interpretation of the "30% drop off", the formula you are using is incorrect, in the sense that the 30% is always based on the FIRST month which is C5*D5.

If you are deducting 30% based on C5*D5, then in the fourth month, it is already 120% which can mean that all 40 policy holders discontinued with their plan.

So if 30% is deducted off the current month, yes you can go negative, but the figures are realistic.

Here is the completed and modified file I made for you:

In that file, I corrected the formula that is suppose to deduct 30% for the drop off so that the 30% is taken from the previous month value, not the original value.

What I did was instead of deducting 30% from the previous month value, I took 70% and then added to the original formula that you have.

For instance, you have the formula:
=E5+(C5*D5)-(C5*D5*0.3)

=E5+F2

Where F2 represents 70% of previous month's value since this is equivalent to adding the value less 30%. Therefore, all operations now are addition.

The sheet called 30%DropOff is a complete copy of your original work correcting only the calculation for the 30% Drop Off. Instead of calculating it always from the first month, it is calculated based on the value on the previous month.

I also created a sheet called 30%DropOff Income_Only. This only reflects all income figures, that is, payment from policy holders. There is no expenses deducted on the thought that commission and acquisition cost are incurred only in the first month.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

Thank you!

Best regards,
Jess
Customer: replied 3 years ago.

Hi,

It doesn't seem as if you are adding the previous month or have I missed something?

Andrew,

Thank you for writing back. The original sheet that you gave me is now named "Original Sheet". Now, what I created I named the sheet as 30%DropOff. It contains ALL of your formulas except that I corrected the application of the 30% drop off. Instead of calculating 30% off the original figure (C5*D5, for example), the 30% is based on the previous month.

For instance:

Month 1: 100
Month 2: 70 (70% of 100)
Month 3: 49 (70% of 70)
Month 4: 34.3 (70% of 49)

So instead of taking 30% OFF of 100 always, it is taken in the previous month.

What are you referring to about not adding the previous month? Are you referring to the "Income_Only" sheet? Well, yes, that is how I understood the scenario. The income for the second to the twelfth month are taken and no expenses incurred for commission and acquisition cost since I believe these costs are only incurred once and that is in the first month.

If you take commission and acquisition cost every month, then the sheet I created called Income_Only is incorrect.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!