Projected Cash Flows: Present and Future Worth Calculations

  • #1
LittleJonJon
5
1
Consider the following income and expenditure of a project over past 23 years and the interest rate of 12% compounded annually.



(1) Plot the Cash Flow

(2) Using excel formulas, calculate the present worth of the project.

(3) Using excel formulas, calculate future worth of the project at the end of year 2023.

imagr.png


1. Present Worth (PW)
The present worth of each cash flow is calculated using the formula:



where:
CF is the cash flow for the year.
i = 0.12 (the annual interest rate).
n is the number of years since 2000

Example Calculations
For 2000

For 2001

For 2003


By summing the present worths of all cash flows:


2. Future Worth (FW)
The future worth of each cash flow is calculated using the formula:



where:
CFis the cash flow for the year.
i = 0.12 (the annual interest rate).
2023 Year is the number of years until 2023.

Example Calculations
For 2000

For 2001

For 2003


By summing the future worths of all cash flows:


Results
Present Worth (PW): -110.11
*Future Worth (FW): -1492.28

Is this the right method for solving this problem? Is there a simpler approach? Can it be solved using Excel functions? e.g.
Present worth =NPV(i%,second_cell:last_cell) + first_cell
Future worth = FV(i%, n, A, P) ?
 
Physics news on Phys.org
  • #2
For 2003, you have 8200/(1.12^3) = 5835.71. I get 5836.60.
 
Last edited:
  • Like
Likes LittleJonJon
  • #3
In your example calculations for FW, you have 1.12^23 as 11.919.
I suggest you enter this into an Excel cell: =POWER(1.12,23)

I get the same values of -110.11 and -1492.28 for the worth of the project as of 2000 and 2023 respectively.
 
Last edited:
  • Like
Likes LittleJonJon
  • #4
LittleJonJon said:
Is this the right method for solving this problem?
Probably not, although it is hard to say because the terms "present worth" and "future worth" are not well defined. I assume they mean "net present value (NPV)" and "net future value (NFV)" respectively.

LittleJonJon said:
Is there a simpler approach? Can it be solved using Excel functions?
It certainly can: the Excel NPV function was were designed for this.

LittleJonJon said:
Present worth =NPV(i%,second_cell:last_cell) + first_cell
Why are you treating the first amount differently? Are you confusing this with questions that have asked something like "$6,543 is invested in a project. Over the next 22 years the project produces cash flows of $2,300, 0, £8,200..."? The correct answer is simply =NPV(i%,first_cell:last_cell).

LittleJonJon said:
Future worth = FV(i%, n, A, P) ?
The FV function doesn't work with a range like NPV does, so not so helpful here. However o
nce you have calculated an NPV there is a simple conversion to a NFV by multiplying by a constant (although it is easy to get an off-by-one error in the constant)

As a side-note, in the real world we normally use the XNPV function as it avoids any confusion over payements at the end/beginning/middle of periods.
 
  • Informative
Likes FactChecker
  • #5
.Scott said:
I suggest you enter this into an Excel cell: =POWER(1.12,23)
Why? It is simpler to enter =1.12^23.
 
  • #6
pbuk said:
Why are you treating the first amount differently?
That is the formula from the textbook.
 
  • #7
pbuk said:
Why? It is simpler to enter =1.12^23.
Obviously, he should enter whichever one he wants. What's important is that he compares it to the value that he has come up with.

Consistent with what @FactChecker mentioned:
In the Excel formula: Present worth =NPV(i%,second_cell:last_cell) + first_cell
The Excel NPV function treats the first value as 1 time period past the "present" period.
So, if the first cell contains the "present" period, it needs to be handled separately.
 
  • Like
Likes LittleJonJon
  • #8
.Scott said:
Obviously, he should enter whichever one he wants. What's important is that he compares it to the value that he has come up with.

Consistent with what @FactChecker mentioned:
In the Excel formula: Present worth =NPV(i%,second_cell:last_cell) + first_cell
The Excel NPV function treats the first value as 1 time period past the "present" period.
So, if the first cell contains the "present" period, it needs to be handled separately.
I did =NPV(12%,B3:B25)+B2 = 110.11
=FV(i%,n,A,P) = SUM(E2,E,25)=-1492
 
  • #9
.Scott said:
The Excel NPV function treats the first value as 1 time period past the "present" period.
No, it treats the first value as occurring at the end of the first period - it treats every nth value as occurring at the end of the nth period which is how net present values are normally calculated. The way you are calculating it treats all cash flows as occurring at the beginning of the respective period.
 
  • Informative
Likes FactChecker
  • #10
updated information
Consider the following income and expenditure of a project over past years and expected in future years. The income and expenditures happen on the last day of each year. The present time is at the end of 2024. The interest rate of 12% compounded annually.



2- Using excel formulas, calculate the present worth of the project.

3-Using excel formulas, calculate future worth of the project at the end of year 2033.
 
  • #11
That makes a lot more sense and removes all ambiguities.

LittleJonJon said:
The present time is at the end of 2024. The interest rate of 12% compounded annually.
2- Using excel formulas, calculate the present worth of the project.
So the discount factor for each year is . This is of course equivalent to the simpler but by convention (and getting things consistently right in financial modelling is all about following conventions) we use the first expression.

LittleJonJon said:
3-Using excel formulas, calculate future worth of the project at the end of year 2033.
Similarly the discount factor for each year here is .

Both the NPV and XNPV functions discount back to the start of the period that ends with the cash flow in the first cell so using either of these is not simple here.
 
  • Like
Likes LittleJonJon
  • #12
pbuk said:
That makes a lot more sense and removes all ambiguities.


So the discount factor for each year is . This is of course equivalent to the simpler but by convention (and getting things consistently right in financial modelling is all about following conventions) we use the first expression.


Similarly the discount factor for each year here is .

Both the NPV and XNPV functions discount back to the start of the period that ends with the cash flow in the first cell so using either of these is not simple here.
Okay I took your advice and
2010-2023 years in the past (their worth should be higher considering the interests rate) and values in 2025-2033 that their worth should be lower in 2024.) This is because we are discounting the future amount back to the present year (2024). The formula correctly accounts for the time value of money,.

What if we use present values calculated by individual formula
(cashflow/(1+r)^n)
calculations per cell;
B2 / (1 + 0.12)^(A2 - A16)
B3 / (1 + 0.12)^(A3 - A16)
..........................................................
B25 / (1 + 0.12)^(A25 - A16)
=sum() cell to add all those values and for future we do similar formula
(cashflow*(1+r)^n) )
B2*(1+0.12)^(2033-A2)
. ................................ –
B25*(1+0.12)^(2033-A25) =sum() cell to add all those values.
Would this be valid?
 

Attachments

  • discountFlows.png
    discountFlows.png
    43.1 KB · Views: 3
Last edited:
  • Like
Likes pbuk
  • #13
LittleJonJon said:
Would this be valid?
Looks OK. Note that instead of B2 / (1 + 0.12)^(A2 - A16), if you enter B2 / (1 + 0.12)^(A2 - A$16) (note the $ sign) you can copy this code down to the rows below and it will keep the reference to A16 static.
 
  • Like
Likes LittleJonJon

Similar threads

Back
Top