Question

Prepare an amortization table for a house you would like to purchase. Prepare it for monthly for two years of the 30 years. Decide how much you can afford knowing it should be 1/3 of your income. Your income is 268,000. You have 30 years and 5% interest. How would that change if you choose biweekly payments instead of monthly. Give an example of each. Create another spreadsheet in the same workbook to show your answer.

Answer 1

Due to character limit I am attaching the image for amortization schedule of biweekly payment.

Amortization Schedule	A	B	C=A*5%/12	D=B-C	E=A-D		Income	268,000.00
Monthly Payments	Beginning	Amount Paid	Interest Owed	Principal paid	Ending		I can afford 1/3rd
1	16,641,157.17	89,333.33	69,338.15	19,995.18	16,621,161.99		i.e. monthly payment will be			89,333.33	268000/3
2	16,621,161.99	89,333.33	69,254.84	20,078.49	16,601,083.50
3	16,601,083.50	89,333.33	69,171.18	20,162.15	16,580,921.35		PMT=	PV*i
4	16,580,921.35	89,333.33	69,087.17	20,246.16	16,560,675.19			1-(1+i)^-n
5	16,560,675.19	89,333.33	69,002.81	20,330.52	16,540,344.67
6	16,540,344.67	89,333.33	68,918.10	20,415.23	16,519,929.43		PMT=	Monthly payment	89,333.33
7	16,519,929.43	89,333.33	68,833.04	20,500.29	16,499,429.14		PV=	Loan amount	?
8	16,499,429.14	89,333.33	68,747.62	20,585.71	16,478,843.43		n=	no. of payments	30*12=	360.00
9	16,478,843.43	89,333.33	68,661.85	20,671.49	16,458,171.94		i	5%/12	0.0041667
10	16,458,171.94	89,333.33	68,575.72	20,757.62	16,437,414.33
11	16,437,414.33	89,333.33	68,489.23	20,844.11	16,416,570.22
12	16,416,570.22	89,333.33	68,402.38	20,930.96	16,395,639.26		89333.33	PV*0.00416666666666667
13	16,395,639.26	89,333.33	68,315.16	21,018.17	16,374,621.09			1-(1+0.00416666666666667)^-360
14	16,374,621.09	89,333.33	68,227.59	21,105.75	16,353,515.35
15	16,353,515.35	89,333.33	68,139.65	21,193.69	16,332,321.66		89333.33	PV
16	16,332,321.66	89,333.33	68,051.34	21,281.99	16,311,039.67		0.004166667	1-(1.00416666666666667)^-360
17	16,311,039.67	89,333.33	67,962.67	21,370.67	16,289,669.00
18	16,289,669.00	89,333.33	67,873.62	21,459.71	16,268,209.29		21439999.2	PV
19	16,268,209.29	89,333.33	67,784.21	21,549.13	16,246,660.16			1-0.223826595641891
20	16,246,660.16	89,333.33	67,694.42	21,638.92	16,225,021.24
21	16,225,021.24	89,333.33	67,604.26	21,729.08	16,203,292.16		21439999.2	PV
22	16,203,292.16	89,333.33	67,513.72	21,819.62	16,181,472.55			0.776173404	0.7761734
23	16,181,472.55	89,333.33	67,422.80	21,910.53	16,159,562.02
24	16,159,562.02	89,333.33	67,331.51	22,001.82	16,137,560.19		PV	21439999.2*0.776173404358109
			1,640,403.02				PV	16,641,157.17	Loan Value

Point to Note:
Loan value will be greater by $ 1,395,468.97 if bi weekly payment plan adopted.
Interest paid for first 2 year will be more by $ 136,973.11 if bi weekly payment plan adopted.