## $140,000 over 3 years or $160,000 over 5 years

### $140,000 over 3 years or $160,000 over 5 years

Which is better: $140,000 paid over the next 36 months or $160,000 paid over the next 60 months. Well, clearly $160,000 is better than $140,000 but what is the interest rate on the deferred funds to get the additional $20,000.

Bill

Bill

### Re: $140,000 over 3 years or $160,000 over 5 years

Use Excel and read about the Internal Rate of Return (IRR) function and Net Present Value (NPV).vanb wrote:Which is better: $140,000 paid over the next 36 months or $160,000 paid over the next 60 months. Well, clearly $160,000 is better than $140,000 but what is the interest rate on the deferred funds to get the additional $20,000.

Bill

-M

### Re: $140,000 over 3 years or $160,000 over 5 years

14.9%. Anything below that, $160,000 over 60 months is the better deal.vanb wrote:what is the interest rate on the deferred funds to get the additional $20,000.

### Re: $140,000 over 3 years or $160,000 over 5 years

Jeez now he won't learn anything!!grunel wrote:14.9%. Anything below that, $160,000 over 60 months is the better deal.vanb wrote:what is the interest rate on the deferred funds to get the additional $20,000.

Basically, which is better depends on how high your discount rate is.

Here's my calculation (I MAKE NO GUARANTEES AS TO ACCURACY - DOUBLE CHECK THIS WITH ANOTHER SOURCE!!!!!) of the NPVs of the two cash streams, depending on your discount rate:

Code: Select all

```
0% $140,000 $160,000
1% $137,864 $156,002
2% $135,773 $152,140
3% $133,725 $148,406
4% $131,720 $144,798
5% $129,756 $141,309
6% $127,832 $137,935
7% $125,947 $134,672
8% $124,101 $131,516
9% $122,293 $128,462
10% $120,521 $125,508
11% $118,786 $122,648
12% $117,085 $119,880
13% $115,418 $117,200
14% $113,785 $114,605
15% $112,184 $112,092
16% $110,615 $109,658
17% $109,077 $107,299
18% $107,569 $105,014
19% $106,091 $102,799
20% $104,642 $100,652
```

So, the indifference point is around 15% compounded monthly (which I believe is closer to 16% on an annual basis).

Basically, unless you've got a very high effective interest rate, take the larger amount over the longer term.

In real life, if this is some sort of debt settlement or something like that, you would have to take credit issues and taxes and such into account as applicable.

Code: Select all

```
==== Using NPV() ========================
Effective Nom Periodic
Annual Int Interest
Yield Rate Rate Cash Flow 1 Cash Flow 2 Difference
0.00% 0.00% 0.00% $140,000.00 $160,000.00 ($20,000.00)
1.00% 1.00% 0.08% $137,873.89 $156,020.36 ($18,146.47)
2.00% 1.98% 0.17% $135,810.47 $152,208.38 ($16,397.91)
3.00% 2.96% 0.25% $133,807.20 $148,554.95 ($14,747.74)
4.00% 3.93% 0.33% $131,861.68 $145,051.56 ($13,189.88)
5.00% 4.89% 0.41% $129,971.62 $141,690.23 ($11,718.62)
6.00% 5.84% 0.49% $128,134.83 $138,463.52 ($10,328.69)
7.00% 6.78% 0.57% $126,349.27 $135,364.43 ($9,015.16)
8.00% 7.72% 0.64% $124,612.96 $132,386.41 ($7,773.45)
9.00% 8.65% 0.72% $122,924.02 $129,523.30 ($6,599.28)
10.00% 9.57% 0.80% $121,280.67 $126,769.33 ($5,488.66)
11.00% 10.48% 0.87% $119,681.22 $124,119.08 ($4,437.86)
12.00% 11.39% 0.95% $118,124.04 $121,567.45 ($3,443.41)
13.00% 12.28% 1.02% $116,607.60 $119,109.64 ($2,502.04)
14.00% 13.17% 1.10% $115,130.40 $116,741.13 ($1,610.72)
15.00% 14.06% 1.17% $113,691.06 $114,457.67 ($766.61)
16.00% 14.93% 1.24% $112,288.22 $112,255.24 $32.98
17.00% 15.80% 1.32% $110,920.60 $110,130.07 $790.53
18.00% 16.67% 1.39% $109,586.97 $108,078.60 $1,508.37
19.00% 17.52% 1.46% $108,286.17 $106,097.44 $2,188.72
20.00% 18.37% 1.53% $107,017.06 $104,183.43 $2,833.63
0.15 =NOMINAL(E25,12) =F25/12 =NPV(G25,B$11:B$46) =NPV(G25,C$11:C$70) =H25-I25
Goal Seek:
Set the cell in the last column to 0 by adjusting the cell in the first column.
15.96% 14.90% 1.24% $112,346.88 $112,346.88 ($0.00)
========= Using PV() =========================
Goal Seek:
Set the cell in the last column to 0 by adjusting the cell in the first column.
15.96% 14.90% 1.24% $112,346.88 $112,346.88 ($0.00)
0.15957668240024 =NOMINAL(E36,12) =F36/12 =PV(G36,36,-140000/36) =PV(G36,60,-160000/60) =H36-I36
```

Cells C$11:C$70 = the monthly payment for 160,000 over 60 months = 2666.666667

Column E: Effective annual yield ("compounded")

Column F: Nominal interest rate, =nominal(), periodic interest rate "un"-compounded for the year

Column G: periodic interest over the period, =nominal/12, "monthly"

Columns H, I: The cash flows, =npv() or =pv()

Column J: Cash flow difference, =H - I

===========================================

First, read these 2 excellent tutorials from Investopedia:

Understanding The Time Value Of Money and Anything But Ordinary: Calculating The Present And Future Value Of Annuities

The Net Present Value part of the table shows how the difference between the two cash flows decreases as the interest rate increases. The "point of indifference", as psteinx noted, is where it's no longer worth it to take the 160,000 and occurs where the difference is zero.

To nail this exactly, use Excel's Goal Seek function (refer to Excel's help file if needed) to set the difference to zero by adjusting the annual interest rate.

This occurs at the monthly interest rate of 14.9% (compounded monthly) which agrees with psteinx and grunel. Note that grunel's answer didn't say whether this was the monthly rate or effective annual yield (it's the monthly rate).

Excel's help file explains the difference between using PV() and NPV().

==========================

On a related note, I had a tough time using IRR() (Internal Rate of Return) with the textbook examples (Chapter 4) until I realized that IRR() uses a built-in optimizer which drives the NPV() to zero. IOW, you need to include the price of the investment so that the total of inflows and outflows is zero.

See XL: Use of the IRR Function in Excel

### Quick, rough calculation

A series of payments over a short time period are approximately equivalent to a single payment in the middle of that time period. Thus you are asking to compare approximately $140,000 received 18 months from now versus $160,000 received 30 months from now. To earn $20,000 in one year on $140,000 requires a 14.3% rate of return, which is close to the 16% that was calculated more precisely.

- DaveH
**Posts:**677**Joined:**Fri May 16, 2008 10:35 am**Location:**Los Angeles, Bangkok, Louisiana, New York City

I think it was a great boglehead moment when you mentioned that the textbook was usedLadyGeek wrote:I didn't know how to do this either. So, I broke out my only textbook, Fixed Income Mathematics by Frank J. Fabozzi (used), and dug into it using Excel to reproduce the examples in the book.

The fewer our wants, the nearer we resemble the gods. — Socrates

### Re: Quick, rough calculation

Linuxizer recommended it. Best $10 I ever spent (including shipping). The next section starts bonds...DaveH wrote:I think it was a great boglehead moment when you mentioned that the textbook was used

How did you come up with this "rule of thumb"? I couldn't find any mention of it. Wikipedia discusses rules of thumb for doubling investments. Probably related to that, but worked backwards somehow. There's most likely an ln(2) ratio (69%) involved (period/2 ?).grabiner wrote:A series of payments over a short time period are approximately equivalent to a single payment in the middle of that time period. Thus you are asking to compare approximately $140,000 received 18 months from now versus $160,000 received 30 months from now. To earn $20,000 in one year on $140,000 requires a 14.3% rate of return, which is close to the 16% that was calculated more precisely.

Good point. I think all it would take is to have an extra column to calculate the impact of the marginal rate for each period. NPV() is designed to handle uneven payments. Hence the need to have a good spreadsheet. What-if scenarios are easy to do.strafe wrote:Don't forget to consider marginal tax rates. The progressive nature of marginal tax rates with respect to annual income penalizes people whose income is uneven from year to year.

BTW, I updated the wiki to add the Investopedia tutorials. Please see Financial Websites and Blogs on the Bogleheads Wiki.

### Re: Quick, rough calculation

If an investment grows at a monthly rate of r, then the value after k months of $1 now and $1 after 2k months is (1+r)^k + (1+r)^(-k). I am approximating this by $2, which is correct to first order in r and k, and is thus a good estimate if r and k are small.LadyGeek wrote:How did you come up with this "rule of thumb"? I couldn't find any mention of it.grabiner wrote:ot;]A series of payments over a short time period are approximately equivalent to a single payment in the middle of that time period. Thus you are asking to compare approximately $140,000 received 18 months from now versus $160,000 received 30 months from now. To earn $20,000 in one year on $140,000 requires a 14.3% rate of return, which is close to the 16% that was calculated more precisely.

The closest analogy is the Rule of 78 for estimating the current balance due on a loan. For a 12-month loan, you pay approximately 12/78 of the interest in the first month, 11/78 in the second month, and so on down to 1/78 in the last month. This is a good approximation for a short-term, low-rate loan; it is also popular with lenders because it favors the lender, overestimating the balance due.

### Re: Quick, rough calculation

I assume you mean (1+r)^k + (1+r)^(2k)? Now I see it, thanks. An assumption of small r and k for FV = P(1+r)^k.grabiner wrote:If an investment grows at a monthly rate of r, then the value after k months of $1 now and $1 after 2k months is (1+r)^k + (1+r)^(-k). I am approximating this by $2, which is correct to first order in r and k, and is thus a good estimate if r and k are small.

The closest analogy is the Rule of 78 for estimating the current balance due on a loan. For a 12-month loan, you pay approximately 12/78 of the interest in the first month, 11/78 in the second month, and so on down to 1/78 in the last month. This is a good approximation for a short-term, low-rate loan; it is also popular with lenders because it favors the lender, overestimating the balance due.

(1 + small number)^(small number) + (1 + small number)^ (2* small number) = 2

I also now know to be wary of loans that use Rule of 78. Interesting history behind it (I did a Google search).

- market timer
**Posts:**6390**Joined:**Tue Aug 21, 2007 1:42 am

### Re: Quick, rough calculation

Think of it like the center of mass in physics.grabiner wrote:If an investment grows at a monthly rate of r, then the value after k months of $1 now and $1 after 2k months is (1+r)^k + (1+r)^(-k). I am approximating this by $2, which is correct to first order in r and k, and is thus a good estimate if r and k are small.LadyGeek wrote:How did you come up with this "rule of thumb"? I couldn't find any mention of it.grabiner wrote:ot;]A series of payments over a short time period are approximately equivalent to a single payment in the middle of that time period. Thus you are asking to compare approximately $140,000 received 18 months from now versus $160,000 received 30 months from now. To earn $20,000 in one year on $140,000 requires a 14.3% rate of return, which is close to the 16% that was calculated more precisely.

The closest analogy is the Rule of 78 for estimating the current balance due on a loan. For a 12-month loan, you pay approximately 12/78 of the interest in the first month, 11/78 in the second month, and so on down to 1/78 in the last month. This is a good approximation for a short-term, low-rate loan; it is also popular with lenders because it favors the lender, overestimating the balance due.

### Re: Quick, rough calculation

No, I do mean for the exponents to be -k and k. The exponent of +k is used to get the value 2k months in the future of $1 k months in the future, and the exponent of -k is used to get the present value of $1 k months in the future. The average of the present value and the value 2k months in the future is close to the value k months in the future. The math isLadyGeek wrote:I assume you mean (1+r)^k + (1+r)^(2k)? Now I see it, thanks. An assumption of small r and k for FV = P(1+r)^k.grabiner wrote:If an investment grows at a monthly rate of r, then the value after k months of $1 now and $1 after 2k months is (1+r)^k + (1+r)^(-k). I am approximating this by $2, which is correct to first order in r and k, and is thus a good estimate if r and k are small.

The closest analogy is the Rule of 78 for estimating the current balance due on a loan. For a 12-month loan, you pay approximately 12/78 of the interest in the first month, 11/78 in the second month, and so on down to 1/78 in the last month. This is a good approximation for a short-term, low-rate loan; it is also popular with lenders because it favors the lender, overestimating the balance due.

(1 + small number)^(small number) + (1 + small number)^ (2* small number) = 2.

(1+small number)^(-small number) + (1+small number)^(-small number)=2.

And the reason that this is a good approximation is that it is accurate to the first order. If you expand (1+r)^k by the binomial theorem, the first few terms are

1 + rk + (rk)^2/2 + (rk)^3/6 + ...

and (1+r)^(-k) likewise expands as

1 - rk + (rk)^2/2 - (rk)^3/6 + ...

so the sum is

2 + (rk)^2 + (rk)^4/12 + ...

If r and k are small, then (rk)^2 is much smaller and can be ignored in an approximation.

market timer - The insight that this is analogous to CM (Center of Mass) helped me understand the overall concept.

This is a great analogy! I assume that "weighted cost" is similar to "Center of Mass" in other areas as well.

I never let a person's college degree or company title affect my decision to ask a question. If you ask nicely, people will be glad to provide answers. They may also enjoy sharing the knowledge.

Mathematicians have their own terminology. So do engineers. Mathematics is the common language- it's the application which drives everyone crazy.