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__ 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 that this assumes that investment period is greater or equal to 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)*

## 0 Comments