Portfolio Capitalization Rate Template

	B	C	E	F	G	H	I
1
2				This is a resource from Creating Portfolio Debt Standards from the Debt Policy Series from All About Debt Management. To use this sheet, first create a copy. The values in columns G and H can be edited by changing the values in the gray cells. You can also update these values by overriding the formulas and hardcoding a value. The LTV in Column I represents the LTV you would need to close at for it to support the "Bad Times" assumptions. In the other words, Asset 1 can support the same loan amount at 48% LTV with a 4.08% cap rate as it could a 65% LTV at a 5.5% Cap Rate.
3
4
5
6
7
8	Capitalization Rate Solver
9	Capitalization Rate Solver
10				Cap Rate	Cap Rate	LTV	LTV
11			Asset	Purchase	Bad Times Underwriting	Bad Times Underwriting	Max Closing
12	Cap Rate - Bad Times	5.50%	Asset 1	4.08%	5.50%	65.00%	48.22%
13	LTV - Bad Times	65%	Asset 2	4.40%	5.50%	65.00%	52.00%
14			Asset 3	4.04%	5.50%	65.00%	47.75%
15			Asset 4	4.11%	5.50%	65.00%	48.57%
16	Max Portfolio LTV	50.59%	Asset 5	4.67%	5.50%	65.00%	55.19%
17	Max Portfolio LTV	50.59%	Asset 6	4.47%	5.50%	65.00%	52.83%
18			Asset 7	4.73%	5.50%	65.00%	55.90%
19			Asset 8	4.93%	5.50%	65.00%	58.26%
20			Asset 9	4.66%	5.50%	65.00%	55.07%
21			Asset 10	4.19%	5.50%	65.00%	49.52%
22			Asset 11	4.03%	5.50%	65.00%	47.63%
23			Asset 12	4.58%	5.50%	65.00%	54.13%
24			Asset 13	4.25%	5.50%	65.00%	50.23%
25			Asset 14	4.00%	5.50%	65.00%	47.27%
26			Asset 15	3.78%	5.50%	65.00%	44.67%
27			Asset 16	4.18%	5.50%	65.00%	49.40%
28			Asset 17	4.06%	5.50%	65.00%	47.98%
29			Asset 18	3.90%	5.50%	65.00%	46.09%
30			Asset 19	4.57%	5.50%	65.00%	54.01%
31			Asset 20	3.99%	5.50%	65.00%	47.15%
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100