Net Present Value by LoanID and Year

[Rebecca Manning]

Hi - I would like to calculate the net present value of a series of cash flows by loanID and by year. For example, for loanID 1 below, I would like the NPV at Year 0 of all the cash flows from Year 1 through Year 5, then the NPV at Year 1 of all the cash flows from Year 2 through Year 5, etc. Any assistance is greatly appreciated. Thank you!

* Example generated by -dataex-. To install: ssc install dataex clear input byte loanID float year long cash_flow float disc_rate 1 1 10000 .15 1 2 20000 .1 1 3 30000 .12 1 4 40000 .14 1 5 50000 .15 2 1 1000 .15 2 2 2000 .1 2 3 3000 .12 2 4 4000 .14 2 5 5000 .15 end

[Carlo Lazzaro]

Rebecca:
welcome to this forum.
If your query is related to a class/home assignment, please see https://www.statalist.org/forums/help#adviceextras #4. Thanks.

[Rebecca Manning]

Thanks, Carlos. It is actually not part of an assignment but for a research idea. I just wanted to provide a simple example that would be easy to follow.

[Carlo Lazzaro]

Rebecca:
something similar can do the trick:

. input byte loanID float year long cash_flow float disc_rate

       loanID       year     cash_flow  disc_rate
  1. 1 1 10000 .15
  2. 1 2 20000 .1
  3.  1 3 30000 .12
  4. 1 4 40000 .14
  5. 1 5 50000 .15
  6. 2 1 1000 .15
  7. 2 2 2000 .1
  8. 2 3 3000 .12
  9. 2 4 4000 .14
 10. 2 5 5000 .15
 11. end

. g NPV_0= cash_flow/(1+ disc_rate)^0

. g NPV_1= cash_flow/(1+ disc_rate)^ year-1 if year>=2
(2 missing values generated)

. g NPV_2= cash_flow/(1+ disc_rate)^ year-1 if year>=3
(4 missing values generated)

. g NPV_4= cash_flow/(1+ disc_rate)^ year-1 if year>=4
(6 missing values generated)

. g NPV_5= cash_flow/(1+ disc_rate)^ year-1 if year>=5
(8 missing values generated)

.

[Clyde Schechter]

by loanID (year), sort: gen disc_factor = 1/(1+disc_rate) if _n == 1
by loanID (year): replace disc_factor = disc_factor[_n-1]/(1+disc_rate) if _n > 1
by loanID: egen net_present_value = total(cash_flow*disc_factor)

Note: This code assumes that the cash flow associated with year n is received at the end of year n. The calculations would be different if it is received at the beginning, or some specified intermediate time.

[Rebecca Manning]

Thanks! Carlos' is more of what I am looking for - a different NPV for each year of the loan. Do you have an idea for how to do it in a loop so that it can be for any number of years, not just 5?

[Carlo Lazzaro]

Rebecca:
do you mean something along the following lines?

g NPV_0= cash_flow/(1+ disc_rate)^0
. forvalues i = 1(1)4 {
  2. generate NPV_`i' = cash_flow/(1+ disc_rate)^ year-1 if year>=`i'+1
  3. }
. list

     +-----------------------------------------------------------------------------------------+
     | loanID   year   cash_f~w   disc_r~e   NPV_0      NPV_1      NPV_2      NPV_3      NPV_4 |
     |-----------------------------------------------------------------------------------------|
  1. |      1      1      10000        .15   10000          .          .          .          . |
  2. |      1      2      20000         .1   20000   16527.93          .          .          . |
  3. |      1      3      30000        .12   30000   21352.41   21352.41          .          . |
  4. |      1      4      40000        .14   40000   23682.21   23682.21   23682.21          . |
  5. |      1      5      50000        .15   50000   24857.84   24857.84   24857.84   24857.84 |
     |-----------------------------------------------------------------------------------------|
  6. |      2      1       1000        .15    1000          .          .          .          . |
  7. |      2      2       2000         .1    2000   1651.893          .          .          . |
  8. |      2      3       3000        .12    3000   2134.341   2134.341          .          . |
  9. |      2      4       4000        .14    4000   2367.321   2367.321   2367.321          . |
 10. |      2      5       5000        .15    5000   2484.884   2484.884   2484.884   2484.884 |
     +-----------------------------------------------------------------------------------------+

.

Following Clyde's helpful remark, the code above assumes that discounting occurs at the beginning of the year.

[Rebecca Manning]

Thanks, Carlo - that's it! I really appreciate the help!