Calculate present value by loanID

[Xinruo Wang]

Hi,

I have a data set that contains loan payments data. I would like to calculate the present value of these payments for each loan by loanID. I tried to use pvvar. However, it does not allow me to calculate by loanID. How should I do this? Many thanks.

[William Lisowski]

Welcome to Statalist.

This example based on the example in the output of help pvvar might start you in a useful direction.

* 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

levelsof loanID, local(loans)

generate d0_cash_flow = .
foreach loan of local loans {
    pvvar cash_flow disc_rate if loanID==`loan'
    replace d0_cash_flow = cash_flow_d0 if loanID==`loan'
    drop cash_flow_d0
    }
list, noobs sepby(loanID)) abbreviate(12)

. list, noobs sepby(loanID) abbreviate(12)

  +------------------------------------------------------+
  | loanID   year   cash_flow   disc_rate   d0_cash_flow |
  |------------------------------------------------------|
  |      1      1       10000         .15       8695.652 |
  |      1      2       20000          .1       16528.93 |
  |      1      3       30000         .12       21353.41 |
  |      1      4       40000         .14       23683.21 |
  |      1      5       50000         .15       24858.84 |
  |------------------------------------------------------|
  |      2      1        1000         .15       869.5652 |
  |      2      2        2000          .1       1652.893 |
  |      2      3        3000         .12       2135.341 |
  |      2      4        4000         .14       2368.321 |
  |      2      5        5000         .15       2485.884 |
  +------------------------------------------------------+

[Xinruo Wang]

Hi William,
.
Thank you so much for your quick response. I tried the code on partial data, It worked! However, when I ran it on the whole data set, it returned macro length exceeded. I have over 10 million observations, so the local does not work. How can I fix this? Thanks a lot!

[William Lisowski]

Here's a revision that avoids the list of loanIDs, run on the same example data as before. I added quietly before the replace command, you might want to do the same on the pvvar command as well. I would test it by changing the upper limit on the loop to 10000 or so and timing how long that takes before setting it to work on the entire dataset.

// the following assumes your data is already sorted in by loanID and year
generate long loanNum = sum(loanID!=loanID[_n-1])
local maxNum = loanNum[_N]

generate d0_cash_flow = .
forvalues num=1/`maxNum' {
    pvvar cash_flow disc_rate if loanNum==`num'
    quietly replace d0_cash_flow = cash_flow_d0 if loanNum==`num'
    drop cash_flow_d0
    }
list, noobs sepby(loanID) abbreviate(12)

. list, noobs sepby(loanID) abbreviate(12)

  +----------------------------------------------------------------+
  | loanID   year   cash_flow   disc_rate   loanNum   d0_cash_flow |
  |----------------------------------------------------------------|
  |      1      1       10000         .15         1       8695.652 |
  |      1      2       20000          .1         1       16528.93 |
  |      1      3       30000         .12         1       21353.41 |
  |      1      4       40000         .14         1       23683.21 |
  |      1      5       50000         .15         1       24858.84 |
  |----------------------------------------------------------------|
  |      2      1        1000         .15         2       869.5652 |
  |      2      2        2000          .1         2       1652.893 |
  |      2      3        3000         .12         2       2135.341 |
  |      2      4        4000         .14         2       2368.321 |
  |      2      5        5000         .15         2       2485.884 |
  +----------------------------------------------------------------+

[Clyde Schechter]

This should work:

capture program drop one_loan
program define one_loan
    pvvar cash_flow disc_rate
    exit
end

runby one_loan, by(loanID) status

Note: -runby- is written by Robert Picard and me, and is available from SSC.

You will also find that it is much faster than using a -foreach- loop.

[William Lisowski]

I agree with Clyde, I didn't think as hard as I should have in revising my initial post. The problem with working with easily manageable datasets is that I don't get to exercise my large dataset skills.

[Romalpa Akzo]

If what is needed is just cash_flow_d0 (and present value), instead of using pvvar, direct calculation (using the standard formula) would be much faster for large dataset.

bys loanID (year): gen double cash_flow_d0 = cash_flow/((1+ disc_rate)^_n)
* by loanID: gen double present_value = sum(cash_flow_d0)

[William Lisowski]

I thought pvvar was perhaps doing something fancy, perhaps using year-to-year discount rates to compute a year-to-year0 cumulative discount rate, but I guess not.

In particular, it makes the undocumented assumption that the cash flow occurs at regular intervals with no skipped interval. That is, the variable "year" in the example I took from help pvvar serves no purpose. Romalpa's code shows us how to rectify this situation should it arise.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte loanID float year long cash_flow float disc_rate
2 1 10000 .15
2 2 20000  .1
2 4 30000 .12
2 7 40000 .14
2 9 50000 .15
end

// data with skipped years
pvvar cash_flow disc_rate, generate(d0_v1)
generate d0_v2 = cash_flow/((1+ disc_rate)^_n)
generate d0_v3 = cash_flow/((1+ disc_rate)^(year-year[1]+1))
list, noobs abbreviate(12)

. list, noobs abbreviate(12)

  +----------------------------------------------------------------------------+
  | loanID   year   cash_flow   disc_rate   cash_flow~10      d0_v2      d0_v3 |
  |----------------------------------------------------------------------------|
  |      2      1       10000         .15       8695.652   8695.652   8695.652 |
  |      2      2       20000          .1       16528.93   16528.93   16528.93 |
  |      2      4       30000         .12       21353.41   21353.41   19065.54 |
  |      2      7       40000         .14       23683.21   23683.21   15985.49 |
  |      2      9       50000         .15       24858.84   24858.84   14213.12 |
  +----------------------------------------------------------------------------+

[Xinruo Wang]

The direct calculation worked very well. Thank you all so much for the inputs!