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

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
vanb
Posts: 26
Joined: Sun Nov 04, 2007 9:26 am

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

Post by vanb »

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
mattsm
Posts: 243
Joined: Mon Jan 11, 2010 10:27 am

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

Post by mattsm »

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
Use Excel and read about the Internal Rate of Return (IRR) function and Net Present Value (NPV).

-M
grunel
Posts: 85
Joined: Mon Jul 27, 2009 9:26 pm

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

Post by grunel »

vanb wrote:what is the interest rate on the deferred funds to get the additional $20,000.
14.9%. Anything below that, $160,000 over 60 months is the better deal.
mattsm
Posts: 243
Joined: Mon Jan 11, 2010 10:27 am

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

Post by mattsm »

grunel wrote:
vanb wrote:what is the interest rate on the deferred funds to get the additional $20,000.
14.9%. Anything below that, $160,000 over 60 months is the better deal.
Jeez now he won't learn anything!! :shock:
psteinx
Posts: 3570
Joined: Tue Mar 13, 2007 2:24 pm

Post by psteinx »

[deleted]
Last edited by psteinx on Thu Feb 11, 2010 7:04 pm, edited 1 time in total.
psteinx
Posts: 3570
Joined: Tue Mar 13, 2007 2:24 pm

Post by psteinx »

I assume you're talking about spreading the amount out equally. i.e. $140,000 over 36 months would be $3888.89/month at the end of each month. The following calculations are based on that assumption.

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
(I believe that my formulas effectively compound monthly. So the APR of the 10% in my table would actually be higher than 10%. Use the above as a ballpark estimate, at best.)

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.
Topic Author
vanb
Posts: 26
Joined: Sun Nov 04, 2007 9:26 am

Post by vanb »

Thank you all. This site is amazing. Ask a question and you have an answer in less than 30 minutes.

Bill
User avatar
LadyGeek
Site Admin
Posts: 71135
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Post by LadyGeek »

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. Then, the problem at hand using psteinx's method to start (thanks!):

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 B$11:B$46 = the monthly payment for 140,000 over 36 months = 3888.888889
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
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
grabiner
Advisory Board
Posts: 29489
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Quick, rough calculation

Post by grabiner »

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.
Wiki David Grabiner
User avatar
DaveH
Posts: 677
Joined: Fri May 16, 2008 10:35 am
Location: Los Angeles, Bangkok, Louisiana, New York City

Post by DaveH »

LadyGeek 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.
I think it was a great boglehead moment when you mentioned that the textbook was used :)
The fewer our wants, the nearer we resemble the gods. — Socrates
strafe
Posts: 1040
Joined: Sat Mar 03, 2007 12:49 pm

Post by strafe »

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.
User avatar
LadyGeek
Site Admin
Posts: 71135
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Quick, rough calculation

Post by LadyGeek »

DaveH wrote:I think it was a great boglehead moment when you mentioned that the textbook was used
Linuxizer recommended it. Best $10 I ever spent (including shipping). The next section starts bonds... :shock:
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.
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 ?).
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.
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.

BTW, I updated the wiki to add the Investopedia tutorials. Please see Financial Websites and Blogs on the Bogleheads Wiki.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
grabiner
Advisory Board
Posts: 29489
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: Quick, rough calculation

Post by grabiner »

LadyGeek wrote:
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.
How did you come up with this "rule of thumb"? I couldn't find any mention of it.
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.
Wiki David Grabiner
User avatar
LadyGeek
Site Admin
Posts: 71135
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Quick, rough calculation

Post by LadyGeek »

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.
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.

(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).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
market timer
Posts: 6411
Joined: Tue Aug 21, 2007 1:42 am

Re: Quick, rough calculation

Post by market timer »

grabiner wrote:
LadyGeek wrote:
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.
How did you come up with this "rule of thumb"? I couldn't find any mention of it.
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.
Think of it like the center of mass in physics.
User avatar
grabiner
Advisory Board
Posts: 29489
Joined: Tue Feb 20, 2007 11:58 pm
Location: Columbia, MD

Re: Quick, rough calculation

Post by grabiner »

LadyGeek wrote:
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.
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.

(1 + small number)^(small number) + (1 + small number)^ (2* small number) = 2.
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 is

(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.
Wiki David Grabiner
User avatar
LadyGeek
Site Admin
Posts: 71135
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Post by LadyGeek »

Got it! grabiner - The more detailed explanation of each +k and -k component (as well as the binomial expansion) made the difference. Thank you.

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.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
alec
Posts: 3105
Joined: Fri Mar 02, 2007 2:15 pm

Post by alec »

I guess now we know why she calls herself ladygeek.

ps - I've learned never to challenge grabiner on any math related topic since he has a PhD in math. :shock:
"It is difficult to get a man to understand something, when his salary depends upon his not understanding it!" - Upton Sinclair
User avatar
LadyGeek
Site Admin
Posts: 71135
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Post by LadyGeek »

You never understand a topic until you try to do it yourself. I'll always ask for help when needed.

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. :)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Post Reply