Simple Equation for Finding Effective Rate in Insurance Payment Schemes

[CRGreathouse]

I was looking over different payment schemes for insurance to decide which was best. I wrote out a simple equation to find the effective interest rate that each charges to accept payments.

I'm not explaining myself well; let me make this more concrete. Suppose you could pay $1000 at the beginning of the year, or make two payments of $520 on Jan 1 and Jul 1. For these deals to be equal, you'd need to make $40 of interest on $480 over six months. The effective monthly rate is such that
$$\left(1+\frac{r}{12}\right)^6=\frac{520}{480}$$
and so
$$r=12\left(\frac{520}{480}\right)^{1/6}-12\approx16.11\%.$$

Unfortunately, my equation didn't work. I thought to solve this in two steps:
1. Given p payments of $b, find an r such that the annual rate $A = $b + $b * r + $b * r^2 + ... + $b * r^{p-1}.
2. With this r, calculate the interest rate as
$$\frac{12}{p}\left(r^{-p/12}-1\right)$$

It looks like I'm doing #1 wrong, because my numbers don't match my double-check spreadsheet (where I calculated the interest rate by 'manual binary splitting' = glorified trial and error). But the process is so simple () that I don't know where I'm going wrong!

I'm using a numerical solver to find an r in [0.7, 0.99] (corresponding to an interest rate of 1% to 43%, which the spreadsheet shows as reasonable) such that
$$A-b\frac{1-r^p}{1-r}=0$$
since b + br + ... br^{p-1} is $b\frac{1-r^p}{1-r}.$ What am I doing wrong?

 

[CRGreathouse]

Numerical example: A = $1,441, the payment due on Jan 1. Quarterly payments of $387.92 are accepted as well, due on Jan 1, Apr 1, Jul 1, and Oct 1. My spreadsheet shows that an interest rate around 20.33% compounded monthly makes the two equal in PV. But

solve(r=.7,.99,1441-387.92*(1-r^4)/(1-r))

yields r = 0.950856... and an interest rate of 5.08% via
$$i=\frac{12}{p}\left(r^{-p/12}-1\right).$$

 

[Dragonfall]

Why are you dividing by 12?

Without dividing by 12, I get r=0.025, so

$$360.25*1.025^3=387.94$$

Times 3 and you get 1551.68 modulo rounding errors.

 

[cepheid]

It looks like you are trying to solve this problem (correct me if I am wrong):

For what interest rate, i (compounded monthly), do p equal payments of $b have a present value of $A?

The first problem is that you used the letter r for two different things in your first post. At first, you used it to mean the interest rate. Later, you used it to mean the common ratio in the geometric series. Let's get our notation straight. How about this:

$$\textrm{common ratio} = r = \left(1 + \frac{i}{12}\right)^{-\frac{12}{p}}$$

Therefore, the relationship between the interest rate and the common ratio is:

$$i = 12(r^{\frac{12}{p}} - 1)$$

When I do this with the numerical example in your second post, I get
r = 0.950857
i = 20.3270%.

Considering that our numerically solved values of r agree, and that my value of i calculated from that value of r agrees with what your spreadsheet told you, I think that you just used the wrong formula to calculate i from r, because you had the two confused.

EDIT: By the way, if you're going to make a spreadsheet to double check your answers, then bear in mind that Excel has an NPV() (net present value) function, as well as a whole ton of other financial functions. Be careful though, to understand exactly what it does so that you give it the right arguments. No need for "manual binary splitting" (whatever that is!).

 

[CRGreathouse]

It looks like you are trying to solve this problem (correct me if I am wrong):

For what interest rate, i (compounded monthly), do p equal payments of $b have a present value of $A?

Right.

The first problem is that you used the letter r for two different things in your first post. At first, you used it to mean the interest rate. Later, you used it to mean the common ratio in the geometric series.

Ah! that was probably the issue.

By the way, if you're going to make a spreadsheet to double check your answers, then bear in mind that Excel has an NPV() (net present value) function, as well as a whole ton of other financial functions. Be careful though, to understand exactly what it does so that you give it the right arguments.

I'm using OOo Calc, actually, but it has roughly the same set of functions.

Of course NPV() isn't valuable to me since it asks for the discount rate, which is what I'm trying to find. Admittedly I have no experience with these commands -- I'm used to doing everything 'by hand' as it were.

No need for "manual binary splitting" (whatever that is!).

That would be setting the rate to 20%, seeing it was too low, setting to 22%, seeing it was too high, setting to 21%, ... Since the spreadsheet does all the calculations it doesn't actually take too long, but it's rather inelegant.

 

[cepheid]

Of course NPV() isn't valuable to me since it asks for the discount rate, which is what I'm trying to find.

Oh yeah, good point.

I seem to recall that for these "reverse" situations, my Engineering Economics* prof recommended using an Excel feature called "Goal Seek" or something like that. I'm not sure if open office has it. It seemed like it did some sort of iterative procedure like what you described, except that it did the iterations automatically until the sought after goal was found, rather than you having to manually tell it to do each and every iteration.

*Man, what a tedious course that was...

 

[CRGreathouse]

Therefore, the relationship between the interest rate and the common ratio is:

$$i = 12(r^{\frac{12}{p}} - 1)$$

When I do this with the numerical example in your second post, I get
r = 0.950857
i = 20.3270%.

Unfortunately, I'm still confused.

r is less than one, so r^3 is also less than 1 so r^3 - 1 is negative. So how did you get a positive interest rate?

 

[CRGreathouse]

Oh yeah, good point.

I seem to recall that for these "reverse" situations, my Engineering Economics* prof recommended using an Excel feature called "Goal Seek" or something like that. I'm not sure if open office has it. It seemed like it did some sort of iterative procedure like what you described, except that it did the iterations automatically until the sought after goal was found, rather than you having to manually tell it to do each and every iteration.

*Man, what a tedious course that was...

OpenOffice has a better goal seeker than Excel, actually. But my main concern here is that I actually understand what I'm doing. I've left the realm of practicality since before my first post -- clearly finding the discount rate to four decimal places is more than I'll ever need for the purpose of actually *buying* the insurance.

And my thanks for your patience.

 

[cepheid]

Unfortunately, I'm still confused.

r is less than one, so r^3 is also less than 1 so r^3 - 1 is negative. So how did you get a positive interest rate?

Yeah I'm a retard and can't do algebra. I think the exponent on the r was supposed to be the multiplicative inverse of -12/p (i.e. -p/12), and not just the negative of it.

Basically, solve for i from the expression for r(i) that I put down in my first post, properly, and you'll see.

I got a positive interest rate because I created an 'i' array first, then created an 'r' array from it based on r(i). I found out where (1-r^p)/(1-r) was equal to 1441/387, and then indexed the i array at that point to see what the corresponding value of i turned out to be. So the method I used was immune to my silly algebra mistake.

 

[CRGreathouse]

OK, that's good. (I should have caught that, but since I was looking for my own mistakes -- having started the thread knowing I was doing something dumb -- I was less vigilant about yours!)

Is there any good way to find r? In Pari I just use the numerical solver I mentioned, but can I do it in Excel/Calc without the goal seeker?

I'd like to be able to write down a nice equation. Here it is in Pari:

rate(b,p,A)=my(r=solve(r=.7,.99,A-b*(1-r^p)/(1-r)));12*(r^(-p/12)-1)
rate(753.56,2,1441)
rate(387.92,4,1441)
rate(127.86,12,1441)