Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 5964

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

This answer was rated:

★★★★★

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:

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.

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.

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.

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)

I made it as: =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!

If you need further assistance, please do not rate me negatively. Instead, please ask me more or reply to me so that I can help you further.

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!

If you need further assistance, please do not rate me negatively. Instead, please ask me more or reply to me so that I can help you further.

Thank you!

Best regards, Jess

Jess M. and other Microsoft Office Specialists are ready to help you