Cash Flow Analysis Calculator

This web page will allow you to calculate the present value of multiple periodic cash flows each defined by a variety of parameters. It is also able to compute a parameter of one or more of these cash flows if the desired present value is given. The resulting cash flows are summarized individually as well as in a spreadsheet form.

I've developed a functional notation to describe a series of related cash flows compactly. This may seem confusing at first, but I've given numerous examples and it allows for a great deal of flexibility in describing cash flows.

The cash flows are all defined in terms of a period. The actual length of that period is up to you and isn't explicitly known by the calculator. The interest rates specified must all be effective rates for the period you are using, so you must adjust discount and growth rates accordingly. In other words, these calculations assume that the interest rates given are for one period with compounding based on that period. If you need to convert an interest rate from one period to another (for example, converting a yearly discount rate to a monthly one), I have a converter that does this.

You may specify the final present value and solve for a specific parameter in the cash flow functions. This is done by substituting 'X' for the parameter for which you want to solve. If you use an 'X' in more than one cash flow function, it must stand for the same parameter in each one. This is illustrated by one of the examples.

I have attempted to insure that the calculations performed here are correct, however this calculator is provided without guarantee.

All of the parameters after amt in these functions have default values, and you may leave off those on the right side whose default values are what you want. For example, if you want: PVI(120,5,0,0,1,0,0):Title you can type: PVI(120,5). The label at the end of a cash flow function is optional. Here are the cash flow functions and a description of the parameters:

Cash Flow Function Definitions: (See examples below.)

PVL(flows, amt, dr, tr, sy, fy, pl, pc) : label --- Present Value of a Limited number of periodic cash flows

PVI(amt, dr, tr, sy, fy, pl, pc) : label --- Present Value of Infinite stream of cash flows

PVS(sy, amt, dr, tr, pl, pc) : label --- Present Value of a Single cash flow in some future period.

Parameter Definitions:

flows

Number of cash flows. The first one will be after sy periods, and they will then occur every fy periods after that. All cash flows occur at the beginning of a period. flows must be a whole number greater than or equal to 1.

amt

The value of the cash flow in the first period (period 0). This amount will increase at the rate of tr and be discounted at the rate of dr each period. Note that the value is for period 0 even if the first cash flow is adjusted forward by sy periods.

dr

The effective discount rate per period expressed as a percentage (e.g. 3.5% is 3.5). The default value of this parameter is 0.0.

tr

The effective rate of increase per period in the cash flows expressed as a percent (3.5% would be 3.5). Defaults to 0.0.

sy

The number of periods to shift the first cash flow out. For example, if the first income was to occur after 5 periods, set sy=5. sy must be a whole number greater than or equal to 0. Defaults to 0 meaning that the first income occurs immediately.

fy

Number of periods between cash flows once they start. fy must be a whole number greater than or equal to 1. For income each period, fy should be set to 1, which is the default.

pl

The probability that any given period's income will be entirely lost. It can also represent the probability that the entire cash flow will be lost. pl is expressed as a number from 0 to 1. It is the percentage chance of loss divided by 100. Defaults to 0.0.

pc

The probability in any given period that the income and all future income will be lost. pc is expressed as a number from 0 to 1. It is the percentage chance of loss divided by 100. Defaults to 0.0.

label

This is an optional label that will appear in the output to identify the particular cash flow. If none is given, then numeric labels will be assigned beginning with 1.

You have two choices as to what you want this to compute. This program can either compute the present value of a set of cash flows or the value of a parameter given the total present value. Check the one you want and if you choose to compute the value of a parameter, input the present value to use. Also, if you choose to calculate a parameter, put an X in place of that parameter in each appropriate cash flow function. If you choose to solve for either flows or fy it will find the nearest whole number.

The Lottery

You win a million dollar lottery paid over 20 years starting now. Each yearly check will be for $50,000. Unfortunately, you'll have to pay 20% ($10,000) of it each year in taxes. Additionally, you hire an accountant to keep track of this windfall. You pay him $500 the first year, and his fee will increase at a rate of 5% a year. What's the present value (discounted at 6%) of your lottery winnings minus the taxes and accounting fees?

Positive Flows:

PVL(20, 50000, 6) : Lottery

Negative Flows:

PVL(20, 10000, 6) : Taxes

PVL(20, 500, 6, 5) : Accountant

The answer is: $477,172.13

Now let's say that each year there's a 1% chance that the organization paying the lottery goes bankrupt, and you won't get any more payments. There's also a 2% chance that they will default on any year's payment. These factors will affect the lottery payments and the taxes, but not the accountant because he has to take care of your previous earnings too. These factors are taken into account as follows:

Positive Flows:

PVL(20, 50000, 6, 0, 0, 1, 0.02, 0.01) : Lottery

Negative Flows:

PVL(20, 10000, 6, 0, 0, 1, 0.02, 0.01) : Taxes

PVL(20, 500, 6, 5) : Accountant

The answer is: $433,063.30

A Mortgage

What is the present value (from the Bank's point of view) of a 15 year mortgage at 7.2% interest with a monthly payment of $1200?

First, figure out the number of periods, which will be 15 (years) times 12 (months in a year) = 180. Then figure out the monthly interest rate, which will be 7.2 divided by 12 or 0.6. Note that this mortgage rate is compounded monthly, so the effective yearly rate is a little larger than 7.2%, but usually it is written as a 7.2% yearly rate. See the example below on converting interest rate periods. Generally mortgages are paid at the start of the month beginning the month after they are set up, so in this case sy will be 1.

Positive Flows:

PVL(180, 1200, 0.6, 0, 1) : Payments

The answer is: $131,861.36

Another Lottery

Your friend Prickly Pete sets up his own lottery. He's selling 1000 tickets at $1.00 a piece. The grand prize winner will receive $100 at the drawing and each year for the next 5 years (a total of six payments). There will be two second prize winners who will receive $50 at the drawing and each year for the next 5 years. The drawing is later today and he offers to sell you the last ticket. What's the present value of buying the ticket using a discount rate of 5%, ignoring taxes and the questionable reputation of your friend?

Here we'll use the pl parameter to indicate the probability of winning. More specifically, the pl parameter is the probability that the cash flow won't happen, so we have to subtract the probabilites of winning from 1 to get the appropriate pl parameter to use. There is a one in one thousand chance of winning the Grand Prize (0.001), so the probability of not winning it is 0.999. There is a two in one thousand chance of winning the Second Prize (0.002), so the probability of not winning that is 0.998.

Positive Flows:

PVL(6, 100, 5, 0, 0, 1, 0.999) : Grand Prize

PVL(6, 50, 5, 0, 0, 1, 0.998) : Second Prize

Negative Flows:

PVS(0, 1) : Ticket

The answer is: $0.07. You actually stand to gain 7 cents a ticket on average by participating in this lottery. Perhaps Pete isn't so prickly after all.

A More Contrived Example: The Adoption

Venus Moneybags has just given birth to a baby girl named Athena. The pregnancy was unplanned, and she doesn't want to keep the baby--she wants you to adopt her. However, even though Venus doesn't want to raise Athena, she does want her daughter to have an expensive upbringing. Venus offers you the following enticements and stipulations to adopt Athena:

She will pay you $20,000 at the beginning of each of the next 21 years, and, as a bonus for a job well done, at the start of the 22nd year, she will pay you an additional lump sum of $40,000 if you manage to flip a coin three times in a row and not get three tails. You may decide how you spend the annual $20,000 allowance, but at the beginning of each of those first 21 years, you must transfer some of this annual sum into a trust fund for the girl. The first year you will deposit $2,000 into the account, and you will increase that amount by 10% each subsequent year. Additionally, the title to a piece of timberland will be transferred to you at the time of the adoption. You can never sell it, but because the land will be managed through sustainable forestry practices, it will generate an income of $5,000 at the start of each year in perpetuity.

Of course, you will be required to pay for Athena's tuition at the elite Marbury School for Girls from first grade (age 6) through twelfth grade (age 18). You don't know exactly how much that will cost, but you do know that the current tuition is $8,000 per year and historically the price has grown at a rate of 6% per year. Venus thinks that after twelve years at such a prestigious academy, the girl ought to pay for college herself.

This all sounds pretty good to you, but Venus informs you that there is one more payment you have to make. Because Athena's father was a champion race walker, each year of the Summer Olympics until Athena is 21, you must make a contribution to them in his honor in the amount of $10,000. (Suppose the next Summer Games are the year after next.) And there's one last catch. Venus herself is an avid hang-glider, which is a risky sport. If Venus dies while hang-gliding, you will no longer receive the $20,000 yearly payment. There is a 2% chance per year that she will die in such an accident.

What is the present value of this whole transaction for you (ignoring taxes) if you use a discount rate of 5%?

Positive Flows:

PVL(21, 20000, 5, 0, 0, 1, 0, 0.02) : Yearly

PVS(21, 40000, 5, 0, 0.125) : Lump

PVI(5000, 5) : Timber

Negative Flows:

PVL(21, 2000, 5, 10) : Allowance

PVL(12, 8000, 5, 6, 5) : School

PVL(5, 10000, 5, 0, 2, 4) : Olympics

The answer is: $139,567.43

Computing the Value of a Parameter

Your neighbor asks to borrow $10,000 from you with the following repayment plan. He'll pay $1,000 at the end of each year for ten years, and additionally will pay you $4,000 at the end of the tenth year. You can get 6% interest in your bank's money market account. Are you better off lending to your neighbor or keeping your money in the bank? How would you change the terms of the deal so it would be equivalent to leaving your money in the bank? Assume that you're certain that you will be paid back and that you won't need the money before the ten years is up.

There are several ways to decide if this is a better deal than putting your money in the bank. First let's try figuring out the present value of those payments given a discount rate of 6%. If the present value is greater than $10,000 then it's a better deal than leaving your money in the bank and if it's less, then it's not.

Note that this calculator assumes that a cash flow occurs at the beginning of a period. In this example, the cash flows are at the end of the ten years starting in the first year (year 0). For calculation purposes, a cash flow at the end of one period is the same as one at the beginning of the next. Because of this, convert the cash flows in this example to start at the beginning of year 1 (the second period) and go for the next ten years.

Positive Flows:

PVL(10, 1000, 6, 0, 1) : Yearly Payments

PVS(10, 4000, 6) : Lump Sum

This reveals that the present value is only $9,593.67, so you're better off leaving the money in the bank. Now let's try setting the present value to $10,000 and solving for the discount rate. If the computed rate is less than 6%, then we know that by lending the money to the neighbor, we're getting a lower rate than we would by leaving it in the bank. Here's what to enter to figure out the discount rate:

Positive Flows:

PVL(10, 1000, x, 0, 1) : Yearly Payments

PVS(10, 4000, x) : Lump Sum

The discount rate calculated is 5.295%, so this result agrees with the one above, and you're better off leaving your money in the bank. Now, how could your neighbor change his terms to make them equivalent to the bank's as far as earnings?

First, let's try seeing what his yearly payment would have to be to bring up the present value to $10,000 at a 6% discount rate.

Positive Flows:

PVL(10, x, 6, 0, 1) : Yearly Payments

PVS(10, 4000, 6) : Lump Sum

He'd have to pay you $1,055.21 yearly instead of $1,000. If he didn't want to increase his annual payments, by how much would he have to increase the lump sum?

Positive Flows:

PVL(10, 1000, 6, 0, 1) : Yearly Payments

PVS(10, x, 6) : Lump Sum

So, the lump sum must be increased to $4,727.68 to be equivalent.

Converting Interest Rate Periods

Often you'll want to convert an interest rate from one period to another. Since this calculator requires that the discount and growth rates be effective for the period used, in these cases you'll need to convert.

Your friend and neighbor Willet Hale wants to borrow $5,000 to replace the roof on his house. He says that he'd like to pay it off in equal monthly installments over the next 24 months and that you can choose the amount. You decide that since he's your friend and you see his tattered roof out your front window, you'll make the loan at the same rate the bank gives you, which is 5%.

You want to solve for 'X' to get the monthly payment, but you can't use 5% for the discount rate, because you need a discount rate for a month not a year. Dividing it by 12 isn't correct because that doesn't take into account compounding. You can use my rate converter calculator to convert the 5% per year rate to 0.407412% per month. Now just plug it in and solve for 'X'.

Positive Flows:

PVL(24, X, 0.407412) : Payments

The answer is: $218.22