How can I compare two payment streams?

[dmedici]

Here's a practical question: how do I determine which payment stream of child support is most advantageous to me?

Background: I pay my ex-wife $1438/month and will do so for another 37 months. My ex-wife has proposed that I pay her $2000/month for 27 months. How do I determine if her proposal is advantageous to me?

Payment #	Current Payment	Proposed Payment
1	-$1438	-$2000
2	-$1438	-$2000
3	-$1438	-$2000
4	-$1438	-$2000
5	-$1438	-$2000
6	-$1438	-$2000
7	-$1438	-$2000
8	-$1438	-$2000
9	-$1438	-$2000
10	-$1438	-$2000
11	-$1438	-$2000
12	-$1438	-$2000
13	-$1438	-$2000
14	-$1438	-$2000
15	-$1438	-$2000
16	-$1438	-$2000
17	-$1438	-$2000
18	-$1438	-$2000
19	-$1438	-$2000
20	-$1438	-$2000
21	-$1438	-$2000
22	-$1438	-$2000
23	-$1438	-$2000
24	-$1438	-$2000
25	-$1438	-$2000
26	-$1438	-$2000
27	-$1438	-$2000
28	-$1438
29	-$1438
30	-$1438
31	-$1438
32	-$1438
33	-$1438
34	-$1438
35	-$1438
36	-$1438
37	-$1438

<tbody>
</tbody>

 

[PCL]

Have you ever try to do the sum for each column: =SUM(B2:B38) vs =SUM(C2:C38)

 

[dmedici]

The sum of the current payments is $53,206 and the sum of the proposed payments is $54,000. The difference is negligible in my eyes, but just based on the sums one should go with the current plan as it is less. But, a sum of the values provides no insight into which is the better payment stream OVER TIME. On the one hand, the present payments allow me to invest the difference ($652) now. On the other hand, the proposed plan would allow me to invest $1438 in payments 28 thru 37, and therefore over the same number of payment periods (i.e., 37), treating the $1438 in payments 28 thru 37 as income to me (because I am not paying it), the proposed plan seems better. But am I reasoning correctly, and am I using Excel correctly?

 

[PCL]

It looks like more a financial question then an excel one

 

[dmedici]

It's both. We can't just talk about functions. We have to talk about functions used for a particular end result.

 

[Giordano Bruno]

Why not use NPV()

 

[dmedici]

If we use NPV, do we use it over the same period (37 payments) or over the periods pertinent to the payment streams? Why?

Here is the table over the same period (37 payments).

Payment #	Date	Current	Proposed
1	2015-07	-$1,438	-$2,000
2	2015-08	-$1,438	-$2,000
3	2015-09	-$1,438	-$2,000
4	2015-10	-$1,438	-$2,000
5	2015-11	-$1,438	-$2,000
6	2015-12	-$1,438	-$2,000
7	2016-01	-$1,438	-$2,000
8	2016-02	-$1,438	-$2,000
9	2016-03	-$1,438	-$2,000
10	2016-04	-$1,438	-$2,000
11	2016-05	-$1,438	-$2,000
12	2016-06	-$1,438	-$2,000
13	2016-07	-$1,438	-$2,000
14	2016-08	-$1,438	-$2,000
15	2016-09	-$1,438	-$2,000
16	2016-10	-$1,438	-$2,000
17	2016-11	-$1,438	-$2,000
18	2016-12	-$1,438	-$2,000
19	2017-01	-$1,438	-$2,000
20	2017-02	-$1,438	-$2,000
21	2017-03	-$1,438	-$2,000
22	2017-04	-$1,438	-$2,000
23	2017-05	-$1,438	-$2,000
24	2017-06	-$1,438	-$2,000
25	2017-07	-$1,438	-$2,000
26	2017-08	-$1,438	-$2,000
27	2017-09	-$1,438	-$2,000
28	2017-10	-$1,438	$1,438
29	2017-11	-$1,438	$1,438
30	2017-12	-$1,438	$1,438
31	2018-01	-$1,438	$1,438
32	2018-02	-$1,438	$1,438
33	2018-03	-$1,438	$1,438
34	2018-04	-$1,438	$1,438
35	2018-05	-$1,438	$1,438
36	2018-06	-$1,438	$1,438
37	2018-07	-$1,438	$1,438
Total		-$53,206	-$39,620
NPV		-$24,031	-$26,312

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

And here is the table with the calculations pertinent to the payment streams.

Payment #	Date	Current	Proposed
1	2015-07	-$1,438	-$2,000
2	2015-08	-$1,438	-$2,000
3	2015-09	-$1,438	-$2,000
4	2015-10	-$1,438	-$2,000
5	2015-11	-$1,438	-$2,000
6	2015-12	-$1,438	-$2,000
7	2016-01	-$1,438	-$2,000
8	2016-02	-$1,438	-$2,000
9	2016-03	-$1,438	-$2,000
10	2016-04	-$1,438	-$2,000
11	2016-05	-$1,438	-$2,000
12	2016-06	-$1,438	-$2,000
13	2016-07	-$1,438	-$2,000
14	2016-08	-$1,438	-$2,000
15	2016-09	-$1,438	-$2,000
16	2016-10	-$1,438	-$2,000
17	2016-11	-$1,438	-$2,000
18	2016-12	-$1,438	-$2,000
19	2017-01	-$1,438	-$2,000
20	2017-02	-$1,438	-$2,000
21	2017-03	-$1,438	-$2,000
22	2017-04	-$1,438	-$2,000
23	2017-05	-$1,438	-$2,000
24	2017-06	-$1,438	-$2,000
25	2017-07	-$1,438	-$2,000
26	2017-08	-$1,438	-$2,000
27	2017-09	-$1,438	-$2,000
28	2017-10	-$1,438
29	2017-11	-$1,438
30	2017-12	-$1,438
31	2018-01	-$1,438
32	2018-02	-$1,438
33	2018-03	-$1,438
34	2018-04	-$1,438
35	2018-05	-$1,438
36	2018-06	-$1,438
37	2018-07	-$1,438
Total		-$53,206	-$54,000
NPV		-$24,031	-$29,286

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Giordano Bruno]

NPV or PV will reduce a series of payments to their present value based on a specific rate of interest. This should be the opportunity cost of the money, i.e. what interest rate you could get if the money were invested. The calculations should therefore be based upon the individual payment series. The result is the amount that would be needed to generate such an income/payment stream.

 

[dmedici]

So, suppose you had the following. Which payment stream make better financial sense and why?

Payment #	Date	Current	Proposed
1	2015-07	-$1,200	-$1,850
2	2015-08	-$1,200	-$1,850
3	2015-09	-$1,200	-$1,850
4	2015-10	-$1,200	-$1,850
5	2015-11	-$1,200	-$1,850
6	2015-12	-$1,200	-$1,850
7	2016-01	-$1,200	-$1,850
8	2016-02	-$1,200	-$1,850
9	2016-03	-$1,200	-$1,850
10	2016-04	-$1,200	-$1,850
11	2016-05	-$1,200	-$1,850
12	2016-06	-$1,200	-$1,850
13	2016-07	-$1,200	-$1,850
14	2016-08	-$1,200	-$1,850
15	2016-09	-$1,200	-$1,850
16	2016-10	-$1,200	-$1,850
17	2016-11	-$1,200	-$1,850
18	2016-12	-$1,200	-$1,850
19	2017-01	-$1,200	-$1,850
20	2017-02	-$1,200	-$1,850
21	2017-03	-$1,200	-$1,850
22	2017-04	-$1,200	-$1,850
23	2017-05	-$1,200	-$1,850
24	2017-06	-$1,200	-$1,850
25	2017-07	-$1,200
26	2017-08	-$1,200
27	2017-09	-$1,200
28	2017-10	-$1,200
29	2017-11	-$1,200
30	2017-12	-$1,200
31	2018-01	-$1,200
32	2018-02	-$1,200
33	2018-03	-$1,200
34	2018-04	-$1,200
35	2018-05	-$1,200
36	2018-06	-$1,200
37	2018-07	-$1,200
Total		-$44,400	-$44,400
NPV		-$20,054	-$25,527

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

The above NPV is calculated assuming a 5% return. Here is what the NPV would be for various rates.

Rate	Current NPV	Proposed NPV
1%	-$36,959	-$39,300
2%	-$31,163	-$34,991
3%	-$26,601	-$31,331
4%	-$22,971	-$28,207
5%	-$20,054	-$25,527
6%	-$17,684	-$23,218
7%	-$15,740	-$21,218
8%	-$14,130	-$19,478
9%	-$12,784	-$17,957
10%	-$11,647	-$16,622

<colgroup><col><col><col></colgroup><tbody>
</tbody>

What does all this mean? I'm not looking JUST for a formula, I am looking for you to explain WHY that formula is the correct one to use to figure which payment stream is financially better than the other.

 

[Giordano Bruno]

That's what I tried to do. Future payments are of lower value than present payments because you have the money earing interest in the mean time. The present value is the sum of all future payments calculated at their respective present values. If you can get 5% on the money that you currently have, or if it costs you 5% to borrow money, that is the percentage you should work on. Obviously, the greater the percentage, the lower the present value. If there is 0% then the present value equals the total of the income stream. Hope that helps. If you need more information, the internet is full of it.