Question

✓ Format Painter Formatting Styles Filter Select Table Styles Clipboard Font Alignment Number Cells Editing AC25 1 x ✓ fx oo; ; ACADAE 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 12 Key Assumptions 3 Square footage 1,910 4 Property price ($) 101,500 5 Down payment 10.0% 6 Interest rate 7 Closing costs at start 8,000 8 Broker fee in year 25 5.0% Property Value 10 Yearly appreciation 1.5% 11 Mortgage Balance Net Properts Value 13 Operating Assumptions 14 Rentisq.ft./Inflator 3.00 1.0% 15 Op. coststyr. ($) / Inflat 11,640 1.0% 16 Tax rate 21.0% 17 Depreciation/year ($) 4,524 Cash Floys 19 Rent Income minus: Operating Costs 21 minus: Debt Amortization 22 plus: Interest tax shield plus: Depreciation tax shield minus: Initial Expenses 25 plus: Sale Property in year 25 26 Total Cash Flows IRR NPY @5% 68,760 11,640 69,448 11,756 70,142 11,874 70,843 11,993 71,552 1 2,113 72,267 12,234 72,990 12,356 73,720 12,480 74,457 12,604 75,202 12,730 75,954 12,858 76,713 12,986 77,480 13,116 78,255 13,247 79,038 13,380 79,828 13,514 80,627 13,649 81,433 13,785 82,247 13,923 83,070 14,062 83,900 14,203 84,73985,587 14,345 14,488 86,443 14,633 87,307 14,780 30 Note:Tax Shields are the tax gains from expensing interest or depreciation. The general formula is interest expensex tax rate for interest tax shield L - L L

Answer 1

		Property Price	101,500
		Down Payment(10%)	10,150	(10%*101500)
	Pv	Mortgage Amount	91,350	(101500-10150)
	Rate	Interest Rate	3.90%
	Nper	Number of Years	25
	PMT	Annual Debt Amortization	5,786	(Using PMT function of excel with Rate=3.9%,Nper=25,Pv=91350)
	H	Initial Expense=10150+8000	18,150
	I	Net Sales Value =147251*(1-0.05)	139,907	(in Year 25)
		Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
		Property Value	101,500	103,023	104,568	106,136	107,728	109,344	110,984	112,649	114,339	116,054	117,795	119,562	121,355	123,176	125,023	126,899	128,802	130,734	132,695	134,686	136,706	138,756	140,838	142,950	145,095	147,271
	A	Beginning Balance		91,350	89,127	86,817	84,417	81,923	79,332	76,641	73,844	70,938	67,919	64,781	61,522	58,136	54,617	50,961	47,163	43,216	39,116	34,856	30,429	25,830	21,052	16,087	10,928	5,569
	B	Mortgage Payment		5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786	5,786
	C=A*3.90%	Interest		3,563	3,476	3,386	3,292	3,195	3,094	2,989	2,880	2,767	2,649	2,526	2,399	2,267	2,130	1,987	1,839	1,685	1,526	1,359	1,187	1,007	821	627	426	217
	D=B-C	Principal		2,223	2,310	2,400	2,494	2,591	2,692	2,797	2,906	3,019	3,137	3,259	3,386	3,519	3,656	3,798	3,946	4,100	4,260	4,426	4,599	4,778	4,965	5,158	5,360	5,569
	E=A-D	Ending Mortgage Balance	91,350	89,127	86,817	84,417	81,923	79,332	76,641	73,844	70,938	67,919	64,781	61,522	58,136	54,617	50,961	47,163	43,216	39,116	34,856	30,429	25,830	21,052	16,087	10,928	5,569	(0)
																														F8 x fc =PMT(F6,F7,-F5) G H I J K 101,500 10,150 91,350 3.90% (10%*101500) (101500-10150) Property Price Down Payment(10%) Mortgage Amount Interest Rate Number of Years Annual Debt Amortization Pv Rate Nper PMT 25 5,786 (Using PMT function of excel with Rate=3.9%,Nper=25, Pv=91350) 101,500 10,150 91,350 3.90% (10% 101500) (101500-10150) Pv Rate Nper PMT Property Price Down Payment(10%) Mortgage Amount Interest Rate Number of Years Annual Debt Amortization Initial Expense=10150+8000 Net Sales Value =147251(1-0.05) Year Property Value Beginning Balance Mortgage Payment Interest Principal Ending Mortgage Balance 5,786 (Using PMT function of excel with Rate=3.9%,Nper=25, Pv=91350) 18,150 139,907 (in Year 25) 101,500 103,023 91,350 5,786 3,563 2,223 89,127 104,568 89,127 5,786 3,476 2,310 86,817 106,136 86,817 5,786 3,386 2,400 8 4,417 1 07,728 84,417 5,786 3,292 2,494 81,923 109,344 81,923 5,786 3,195 2,591 79,332 110,984 79,332 5,786 3,094 2,692 76,641 112,649 76,641 5,786 2,989 2,797 73,844 114,339 73,844 5,786 2,880 2,906 70,938 116,054 70,938 5,786 2,767 3,019 67,919 10 117,795 67,919 5,786 2,649 3,137 64,781 C=A*3.90% D=B-C E=A-D 91,350 CASH FLOWS N YEAR Rent Income minus: Operating costs minus: Debt Amortization FEC-21% plus: Interest Tax Shield G=4524.21% plus:Depreciation Tax Shield minus: Initial expense Plus: Sale Property in year25 Total Cash Flows PV=)/(1.054N) Present Value of Total Cash Flow@5 IRR NPV=Sum of PV NPV@5% 68,760 11,640 5,786 748.16 950 69,448 11,756 5,786 729.95 950 3 70,142 11,874 5,786 711.03 950 7 0,843 11,993 5,786 691.37 950 5 71,552 12,113 5,786 670.95 950 72,267 12,234 5,786 649.73 950 72,990 12,356 5,786 627.69 950 8 7 3,720 12,480 5,786 6 04.78 950 9 74,457 12,604 5,786 580.98 950 10 75,202 12,730 5,786 556.25 950 18,150 53,032 50,507 53,585 48,603 54,143 46,771 54,706 45,007 55,274 43,309 55,848 41,674 5 6,426 40,101 57,009 38,586 57,598 37,128 58,192 35,725 (18,150) (18,150) 293% 849,518 Pv Rate Nper PMT Property Price Down Payment(10%) Mortgage Amount Interest Rate Number of Years Annual Debt Amortization Initial Expense=10150+8000 Net Sales Value =147251*(1-0.05) Year Property Value Beginning Balance Mortgage Payment Interest Principal Ending Mortgage Balance 12 15 119,562 64,781 5,786 2,526 3,259 61,522 13 123,176 58,136 5,786 2,267 3,519 54,617 121,355 61,522 5,786 2,399 3,386 58,136 14 125,023 54,617 5,786 2,130 3,656 50,961 16 128,802 47,163 5,786 1,839 3,946 43,216 126,899 50,961 5,786 1,987 3,798 47,163 17 130,734 43,216 5,786 1,685 4,100 39,116 18 132.695 39,116 5,786 1,526 4,260 34,856 19 134,686 34,856 5,786 1,359 4,426 30,429 140,838 21,052 5,786 20 136,706 30,429 5,786 1,187 4,599 25,830 21 138,756 25,830 5,786 1,007 4,778 21,052 821 C=A*3.90% D=B-C E=A-D 4,965 16,087 N 75,954 12,858 15 79,038 13,380 12 76,713 12,986 5,786 503.87 950 13 77,480 13,116 5,786 476.13 950 CASH FLOWS YEAR Rent Income minus: Operating Costs minus: Debt Amortization F=C 21% plus: Interest Tax Shield G=4524.21% plus:Depreciation Tax Shield minus: Initial expense Plus: Sale Property in year25 Total Cash Flows PV=1/(1.05^N) Present Value of Total Cash Flow@5 IRR NPV=Sum of PV NPV@5% 14 78,255 13,247 5,786 447.31 950 16 79,828 13,514 5,786 386.26 950 17 80,627 13,649 5,786 353.94 950 18 81,433 13,785 5,786 320.36 950 19 82,247 13,923 5,786 285.47 950 5,786 20 83,070 14,062 5,786 249.22 950 21 83,900 14,203 5,786 211.55 950 5,786 22 84,739 14,345 5,786 172.41 950 530.56 950 417.37 950 H 58,791 34,374 59,395 33,073 60,005 31,822 60,619 30,617 61,240 29,457 61,865 28,341 62,496 27,267 63,132 26,233 63,774 25,237 64,421 24,279 65,073 23,357 65,731 22,470 TUL Nel Jale Value -14721 10.01 11 12 13 A 14 B 15 C=A*3.90% 16 D=B-C 17 E=A-D Year Property Value Beginning Balance Mortgage Payment Interest Principal Ending Mortgage Balance 1 22 140,838 21,052 5,786 821 4,965 16,087 23 142,950 16,087 5,786 24 145,095 10,928 5,786 426 5,360 5,569 25 147,271 5,569 5,786 217 5,569 (0) 627 5,158 10,928 18 19 20 N 21 CASH FLOWS YEAR Rent Income minus: Operating costs minus: Debt Amortization plus: Interest Tax Shield plus:Depreciation Tax Shield minus: Initial expense Plus: Sale Property in year25 Total Cash Flows Present Value of Total Cash Flow@5 84,739 14,345 5,786 172.41 950 23 85,587 14,488 5,786 131.75 950 24 86,443 14,633 5,786 89.50 950 25 87,307 14,780 5,786 45.61 950 23 B 24 FEC 21% 25 G=4524.21% 26 H 27 28 29 PV=1/(1.05^N) 65,731 22,470 66,394 21,616 67,063 20,794 139,907 207,644 SUM 61,318 849,518 спрага 11 Algiment LuiLily F30 - X A B C F 101,500 fac =IRR(F28:AE28) D | Year Property Value Beginning Balance Mortgage Payment C=A*3.90% Interest D=B-C Principal E=A-D Ending Mortgage Balance G - 1 103,023 91,350 5,786 3,563 2,223 89,127 H 2 104,568 89,127 5,786 3,476 2,310 86,817 I 3 106,136 86,817 5,786 3,386 2,400 84,417 J 4 107,728 84,417 5,786 3,292 2,494 81,923 K 5 109,344 81,923 5,786 3,195 2,591 79,332 91,350 CASH FLOWS YEAR Rent Income minus: Operating Costs minus: Debt Amortization plus: Interest Tax Shield plus:Depreciation Tax Shield minus: Initial expense Plus: Sale Property in year25 Total Cash Flows Present Value of Total Cash Flow@5% 68,760 11,640 5,786 748.16 950 2 69,448 11,756 5,786 729.95 950 3 70,142 11,874 5,786 711.03 950 70,843 11,993 5,786 691.37 950 71,552 12,113 5,786 670.95 950 F=C*21% G=4524*21% 18,150 (18,150) (18,150) 53,032 50,507 53,585 48,603 54,143 46,771 54,706 45,007 55,274 43,309 PV=J/(1.05^N)