Actuarial Outpost GROWTH Formula in Excel
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

#1
09-14-2018, 12:05 PM
 RedSoxJSS SOA Join Date: Nov 2017 Location: New York, NY Studying for Exam FM/2 Favorite beer: Corona Posts: 4
GROWTH Formula in Excel

Hey guys,

I have an Excel question. If you think you can help me or give me other suggestions (within Excel please!), I would be most appreciative.

I am trying to find the trend in premiums (y-values) based on year (x-values) using the GROWTH formula - that's how this process has been done in the past. However, for some years, the premium (y-values) are 0, which leads to a #NUM! error. I also receive this error when using the RSQ formula for the same years.

Does anyone have any ideas about manual calculations I can perform (instead of Excel's formulas) or other suitable functions/reconciliations that may be useful to me?

#2
09-14-2018, 12:42 PM
 AMedActuary Member SOA Join Date: May 2007 College: UCLA Alumni Posts: 391

This is mainly because you can't take a log of 0.

You can try adding a small amount like 0.01 or 1 to all of your premiums then remove it from your predicted value.

The other issue is that these zero premiums are really going to mess with your model (even with a transformation) so depending on how many there are and why they exist, you may want to remove them. Do they exist because it's a small block of members and sometimes, there's no membership and hence no premium? If so, I recommend just removing the years with no premium when using the growth function.

 Tags excel, formula, growth, rsq, statistics