npv calc for customer pay plans
 

I'm thinking that everytime we offer a pay plan to a customer, we lose
money by not collecting payment (premium, since I am in the insurance
industry) in full at the time of sale based on either a present value
or npv calculation. I would like to be able to calculate that loss for
various terms offered, i.e., 25% and 9 installments, 20% deposit and 8
installments, etc. While I think that my organization understands the
value of collecting larger deposits and few installments, I'm not sure
that they ever make the calculation that looks at the loss associated
with the offering of a pay plan vs collecting the balance in full (via
external financing, etc). I would like to do that but I need some help
putting together the calculation. TIA!

npv calc for customer pay plans
 

All you need to do is a present value calculation.

Let's use an example of a normal premium of $10,000 up front, or terms of 25%
down and payments of $900 per month for 9 months. In this example, I put the
down payment in a2, monthly payment in a3, 10% in a4 and the number of payments
in a5. Use any appropriate interest rate to start.

To compare the two options, you calculate a6 =a2+pv(a4/12,a5,-13)

Now do a goal seek to set a6 to 10,000 by varying a4.

In this example, the rate of return is 19%.

Once you've calculated the actual interest rate on the terms offered, management
can decide whether they think they're getting a good deal or not.

--
Regards,
Fred


"Juan Valdez" wrote in message
...
I'm thinking that everytime we offer a pay plan to a customer, we lose
money by not collecting payment (premium, since I am in the insurance
industry) in full at the time of sale based on either a present value
or npv calculation. I would like to be able to calculate that loss for
various terms offered, i.e., 25% and 9 installments, 20% deposit and 8
installments, etc. While I think that my organization understands the
value of collecting larger deposits and few installments, I'm not sure
that they ever make the calculation that looks at the loss associated
with the offering of a pay plan vs collecting the balance in full (via
external financing, etc). I would like to do that but I need some help
putting together the calculation. TIA!