How can I use the Portfolio Manager - Technical Summary?

For any investors who are familiar with Excel or SQL, below you will find a technical summary of how we calculate the expected rate of return used in our Portfolio Manager. 

(Note: Underlined text are functions (Excel and/or SQL)

Inputs

• The number of times that interest is compounded per year (NumberOfCompounds in the following)

Inputs specific for Existing portfolio:

• Existing investment (portfolio value on Dashboard)
• Expected return (ER) of current portfolio (weighted by portfolio value). Lower confidence interval is used for the pessimistic scenario and upper confidence interval value for the optimistic scenario.
• Weighted average outstanding period of the current portfolio

Inputs specific for New portfolio:

• Investment amount (free cash on the account OR promised cash in the flow
• ER of new portfolio (Unweighted ER of simulated purchases from loans put on the market in the last 90 days loans for each risk strategy). Lower confidence interval is used for the pessimistic scenario and upper confidence interval value for the optimistic scenario.
• Deposit per month (average monthly deposit, based on last 12 months’ data)
• Investment Period (in years, client input)

 Interim calculations

• Cash from existing investments

CashFromExistingInvestments = PMT(ER/12; WeightedAverageOutstandingPeriod; -ExistingInvestment) * WeightedAverageOutstandingPeriod

Note: this assumes that the investment period is greater or equal to the weighted average outstanding period. As it might not always be the case, the following is used instead in the future value calculations:

• Cash from existing investments until the end of investment period

CashFromExistingInvestmentsTilEndOfInvestmentPeriod = min(PMT(ER/12; NumberOfCompounds * InvestmentPeriod; -ExistingInvestment) * NumberOfCompounds * InvestmentPeriod; CashFromExistingInvestments)

•  Monthly cash from existing investment

MonthlyCashFromExistingInvestment = CashFromExistingInvestments / WeightedAverageOutstandingPeriod

• Monthly cash from existing investment until then end of investment period

MonthlyCashFromExistingInvestmentsTilEndOfInvestmentPeriod = MonthlyCashFromExistingInvestment

•  Expected monthly return (EMR). This is calculated separately for existing and new portfolio and for both pessimistic and optimistic scenario

EMR = (1+ER)^(1/12)-1

Future value calculations (Note that these are calculated separately for pessimistic and optimistic scenario)

• Future value of principal

FutureValueOfPrincipal = InvestmentAmount * (1+EMR) ^ (NumberOfCompounds * InvestmentPeriod)

• Future value of monthly contributions from existing portfolio

This is divided into two parts: during outstanding period and after outstanding period.

FutureValueOfMonthlyContributionsFromExistingPortfolio_OutstandingPeriod = MonthlyCashFromExistingInvestmentTilEndOfInvestmentPeriod *((1+EMR) ^ (Period)-1 / EMR)

Where Period = min(WeightedAverageOutstandingPeriod; NumberOfCompounds * InvestmentPeriod)

and

FutureValueOfMonthlyContributionsFromExistingPortfolio_AfterOutstandingPeriod =

FutureValueOfMonthlyContributionsFromExistingPortfolio_OutstandingPeriod *(1+EMR) ^ (max((InvestmentPeriod – WeightedAverageOutstandingPeriodYears) * NumberOfCompounds); 0)

• Future value of monthly deposits

FutureValueOfMonthlyDeposits = DepositPerMonth*(((1+EMR) ^ (NumberOfCompounds * InvestmentPeriod) -1)/EMR)

• Total future value

TotalFutureValue = FutureValueOfPrincipal + FutureValueOfMonthlyContributionsFromExistingPortfolio_OutstandingPeriod + FutureValueOfMonthlyContributionsFromExistingPortfolio_AfterOutstandingPeriod + FutureValueOfMonthlyDeposits

Profit calculation

• Net profit

NetProfit = TotalFutureValue - InvestmentAmount – MonthlyDeposits*InvestmentPeriod*12 – ExistingInvestment*PeriodCoefficient

where

PeriodCoefficient = min((InvestmentPeriod*12)/ WeightedAverageOutstandingPeriod; 1)