# How can I compare two payment streams?

#### dmedici

##### New Member
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>

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### PCL

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

#### dmedici

##### New Member
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
It looks like more a financial question then an excel one

#### dmedici

##### New Member

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

#### Giordano Bruno

##### Well-known Member
Why not use NPV()

#### dmedici

##### New Member

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

##### Well-known Member
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
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

##### Well-known Member
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.

Replies
0
Views
396
Replies
5
Views
340
Replies
5
Views
212
Replies
8
Views
386
Replies
1
Views
448

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,951
Messages
5,834,533
Members
430,295
Latest member
amdis

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

### Which adblocker are you using?

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

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