How can I compare two payment streams?

dmedici

New Member
Joined
Jun 20, 2015
Messages
5
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>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Have you ever try to do the sum for each column: =SUM(B2:B38) vs =SUM(C2:C38)
 

dmedici

New Member
Joined
Jun 20, 2015
Messages
5
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
It looks like more a financial question then an excel one
 

dmedici

New Member
Joined
Jun 20, 2015
Messages
5

ADVERTISEMENT

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

dmedici

New Member
Joined
Jun 20, 2015
Messages
5

ADVERTISEMENT

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 #DateCurrentProposed
12015-07-$1,438-$2,000
22015-08-$1,438-$2,000
32015-09-$1,438-$2,000
42015-10-$1,438-$2,000
52015-11-$1,438-$2,000
62015-12-$1,438-$2,000
72016-01-$1,438-$2,000
82016-02-$1,438-$2,000
92016-03-$1,438-$2,000
102016-04-$1,438-$2,000
112016-05-$1,438-$2,000
122016-06-$1,438-$2,000
132016-07-$1,438-$2,000
142016-08-$1,438-$2,000
152016-09-$1,438-$2,000
162016-10-$1,438-$2,000
172016-11-$1,438-$2,000
182016-12-$1,438-$2,000
192017-01-$1,438-$2,000
202017-02-$1,438-$2,000
212017-03-$1,438-$2,000
222017-04-$1,438-$2,000
232017-05-$1,438-$2,000
242017-06-$1,438-$2,000
252017-07-$1,438-$2,000
262017-08-$1,438-$2,000
272017-09-$1,438-$2,000
282017-10-$1,438$1,438
292017-11-$1,438$1,438
302017-12-$1,438$1,438
312018-01-$1,438$1,438
322018-02-$1,438$1,438
332018-03-$1,438$1,438
342018-04-$1,438$1,438
352018-05-$1,438$1,438
362018-06-$1,438$1,438
372018-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 #DateCurrentProposed
12015-07-$1,438-$2,000
22015-08-$1,438-$2,000
32015-09-$1,438-$2,000
42015-10-$1,438-$2,000
52015-11-$1,438-$2,000
62015-12-$1,438-$2,000
72016-01-$1,438-$2,000
82016-02-$1,438-$2,000
92016-03-$1,438-$2,000
102016-04-$1,438-$2,000
112016-05-$1,438-$2,000
122016-06-$1,438-$2,000
132016-07-$1,438-$2,000
142016-08-$1,438-$2,000
152016-09-$1,438-$2,000
162016-10-$1,438-$2,000
172016-11-$1,438-$2,000
182016-12-$1,438-$2,000
192017-01-$1,438-$2,000
202017-02-$1,438-$2,000
212017-03-$1,438-$2,000
222017-04-$1,438-$2,000
232017-05-$1,438-$2,000
242017-06-$1,438-$2,000
252017-07-$1,438-$2,000
262017-08-$1,438-$2,000
272017-09-$1,438-$2,000
282017-10-$1,438
292017-11-$1,438
302017-12-$1,438
312018-01-$1,438
322018-02-$1,438
332018-03-$1,438
342018-04-$1,438
352018-05-$1,438
362018-06-$1,438
372018-07-$1,438
Total-$53,206-$54,000
NPV-$24,031-$29,286

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

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
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

New Member
Joined
Jun 20, 2015
Messages
5
So, suppose you had the following. Which payment stream make better financial sense and why?

Payment #DateCurrentProposed
12015-07-$1,200-$1,850
22015-08-$1,200-$1,850
32015-09-$1,200-$1,850
42015-10-$1,200-$1,850
52015-11-$1,200-$1,850
62015-12-$1,200-$1,850
72016-01-$1,200-$1,850
82016-02-$1,200-$1,850
92016-03-$1,200-$1,850
102016-04-$1,200-$1,850
112016-05-$1,200-$1,850
122016-06-$1,200-$1,850
132016-07-$1,200-$1,850
142016-08-$1,200-$1,850
152016-09-$1,200-$1,850
162016-10-$1,200-$1,850
172016-11-$1,200-$1,850
182016-12-$1,200-$1,850
192017-01-$1,200-$1,850
202017-02-$1,200-$1,850
212017-03-$1,200-$1,850
222017-04-$1,200-$1,850
232017-05-$1,200-$1,850
242017-06-$1,200-$1,850
252017-07-$1,200
262017-08-$1,200
272017-09-$1,200
282017-10-$1,200
292017-11-$1,200
302017-12-$1,200
312018-01-$1,200
322018-02-$1,200
332018-03-$1,200
342018-04-$1,200
352018-05-$1,200
362018-06-$1,200
372018-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.

RateCurrent NPVProposed 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

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,951
Members
414,350
Latest member
ajesh27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top