the TRUE expected principal - Loan Portfolio

There may be an excel function for this, but I am not an accountant, and dont know all the finance functions. How would you do this manually?

Originally Posted by TheDunce

Is there a single formula [...] to determine the true expected principal payment from a loan portfolio [....]. For example, 3 loans, each with an initial amount of $2000, an APR of 59.9%, and terms of 12,6, and 3 months, respectively, end up having a different first-period principal payment ($1054 in this case) as opposed to the weighted average model ($737).

Why are you interested in the "expected principal payment"? That is, what purpose do you have in calculating it?

I cannot imagine a good use for such information.

-----

In any case, what do you mean by "expected principal payment"?

And what is the difference between that and the "true" expected principal payment?

For now, I assume that you mean the mathematical definition of "expected value", which in this case is the arithmetic average (of something), aka the arithetic mean.

But it is unclear what average you want: average per payment; or average per period?

Your reference to the total first-period principal payment ($1054) and the "weighted average" ($737) suggests that you want the average per period.

But in that case, it is unclear how you calculated the "weighted average".

A weighted average per payment makes more sense to me.

See my calculations below. If yours are very different, please attach an Excel file that demonstrates your calculation of the "true expect principal payment" for the example three loans above.

Originally Posted by TheDunce

To do this manually, you would need to amortize every loan [....] using a CUMPRINC function and subtracting the prior period CUMPRINC.

It is not necessary to subtract adjacent CUMPRINC results. Simply use the same starting and ending period number.

The table and explanation below demonstrates my interpretation of your problem. It also demonstrates a simple way to calculate the various averages. Also see the attached Excel file.

	A	B	C	D	E
1		Loan1	Loan2	Loan3
2	Loan Amt	$2,000.00	$2,000.00	$2,000.00
3	Annl Rate	59.90%	59.90%	59.90%
4	#Months	3	6	12
5	Pmt/mo	$734.3025	$393.9299	$225.5448
6
7	Pmt#	Prin Amt	Prin Amt	Prin Amt	TOTAL
8	1	$634.4691	$294.0966	$125.7114	$1,054.2772
9	2	$666.1397	$308.7769	$131.9865	$1,106.9032
10	3	$699.3912	$324.1900	$138.5749	$1,162.1561
11	4		$340.3725	$145.4921	$485.8646
12	5		$357.3628	$152.7545	$510.1173
13	6		$375.2011	$160.3795	$535.5807
14	7			$168.3852	$168.3852
15	8			$176.7904	$176.7904
16	9			$185.6152	$185.6152
17	10			$194.8805	$194.8805
18	11			$204.6082	$204.6082
19	12			$214.8216	$214.8216
20	TOTAL	$2,000.0000	$2,000.0000	$2,000.0000	$6,000.0000
21	Avg/Per	$666.6667	$333.3333	$166.6667	$500.0000
22	Simplest	$666.6667	$333.3333	$166.6667	$500.0000
23
24		Avg/Pmt	Same?
25	Avg	$285.7143
26	Wgt Avg	$285.7143	TRUE
27	Simplest	$285.7143	TRUE

Formulas:
B5:  =PMT(B3/12,B4,-B2)
B8:  =IF($A8<=B$4,-CUMPRINC(B$3/12,B$4,B$2,$A8,$A8,0),"")
B20: =SUM(B8:B19)
B21: =AVERAGE(B8:B19)
B22: =B2/B4
B25: =AVERAGE(B8:D19)
B26: =SUMPRODUCT(B22:D22,B4:D4)/SUM(B4:D4)
B27: =SUM(B2:D2)/SUM(B4:D4)
E8:  =SUM(B8:D8)
E22: =SUM(B2:D2)/MAX(B4:D4)
Copy B5 into C5:D5
Copy B8 into B8:D19
Copy B20:B21 into C20:D21
Copy B22 into C22:D22
Copy E8 into E9:E19

Note that the various averages can be calculated with simply the loan amount and the loan term (number of payments), which avoids the use of the amortization schedule. Specifically:

1. Average principal payment for each loan (row 22) = loan amount / number of payments

2. Average principal payment of all loans (B27) = sum of loan amounts / sum of number of loan terms (total number of payments)

3. Average principal payment per period (E22) = sum of loan amounts / max loan term (number of payments)

You might be interested in the method for calculating the weighted average principal payment of all loans in B26. But the formula in B27 is simpler.

But again, I cannot imagine what the useful value of such information might be.

What use is it to know the "average principal payment"?