Question

Ann got a 30 year Fully Amortizing FRM for $1,000,000 at an annual interest rate of 7% compounded monthly, with monthly payments. After 5 years of payments, Ann can refinance the balance into a 25 year Fully Amortizing FRM at an annual interest rate of 6% compounded monthly, with monthly payments. Refinancing will cost Ann 1 point and $1,500 in closing costs. If Ann refinances into this loan after 5 years, what will be her total cost of refinancing? Answer is 10,913.16. How do I solve this using a financial calculator?

Answer 1

Soln : We have used the Excel to calculate the remianing rincipal at the end of year 5

EMI calculated using the PMT(rate= 7/12%, t = 12*30, -1000000,0)

Interest is calculated using remaining principal *7/12%

Principal Paid monthly = EMI - Interest paid

Remaining Principal = Principal outstanding last mnth - Principal paid monthly

Please refer the table :

Month	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	26	27	28	29	30	31	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
EMI	6,653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02	6653.02
Interest	5833.333333	5828.551799	5823.742372	5818.90489	5814.03919	5809.145106	5804.222474	5799.271126	5794.290896	5789.281614	5784.243111	5779.175217	5774.07776	5768.950568	5763.793467	5758.606284	5753.388842	5748.140964	5742.862474	5737.553193	5732.212941	5726.841538	5721.438801	5716.004549	5710.538596	5705.040759	5699.510852	5693.948686	5688.354074	5682.726828	5677.066755	5671.373666	5665.647367	5659.887664	5654.094363	5648.267268	5642.406182	5636.510905	5630.58124	5624.616985	5618.617939	5612.583898	5606.514658	5600.410015	5594.269761	5588.093689	5581.88159	5575.633254	5569.348469	5563.027023	5556.668702	5550.27329	5543.840572	5537.37033	5530.862345	5524.316396	5517.732263	5511.109722	5504.44855	5497.748521
Principal paid	819.69	824.47	829.28	834.12	838.99	843.88	848.80	853.75	858.73	863.74	868.78	873.85	878.95	884.07	889.23	894.42	899.64	904.88	910.16	915.47	920.81	926.18	931.59	937.02	942.49	947.98	953.51	959.08	964.67	970.30	975.96	981.65	987.38	993.14	998.93	1,004.76	1,010.62	1,016.51	1,022.44	1,028.41	1,034.41	1,040.44	1,046.51	1,052.61	1,058.76	1,064.93	1,071.14	1,077.39	1,083.68	1,090.00	1,096.36	1,102.75	1,109.18	1,115.65	1,122.16	1,128.71	1,135.29	1,141.92	1,148.58	1,155.28
Remaining principal	9,99,180.31	9,98,355.84	9,97,526.55	9,96,692.43	9,95,853.45	9,95,009.57	9,94,160.76	9,93,307.01	9,92,448.28	9,91,584.53	9,90,715.75	9,89,841.90	9,88,962.95	9,88,078.88	9,87,189.65	9,86,295.23	9,85,395.59	9,84,490.71	9,83,580.55	9,82,665.08	9,81,744.26	9,80,818.08	9,79,886.49	9,78,949.47	9,78,006.99	9,77,059.00	9,76,105.49	9,75,146.41	9,74,181.74	9,73,211.44	9,72,235.49	9,71,253.83	9,70,266.46	9,69,273.32	9,68,274.39	9,67,269.63	9,66,259.01	9,65,242.50	9,64,220.05	9,63,191.65	9,62,157.24	9,61,116.80	9,60,070.29	9,59,017.67	9,57,958.92	9,56,893.99	9,55,822.84	9,54,745.45	9,53,661.78	9,52,571.78	9,51,475.42	9,50,372.67	9,49,263.49	9,48,147.83	9,47,025.67	9,45,896.96	9,44,761.67	9,43,619.75	9,42,471.18	9,41,315.90

Now, we can see that at end of 5 years the remianing principal = $941315.90

And as given refinancing cost is 1 point i.e. 1 % of outstanding balnce + 1500

Refinancing cost = 1%* 941315.90 + 1500 = $10,913.16

You can calculate the same in Fin. calculator, for that please input the following values :

Use fn 2nd and put P/Y = 12 press Enter, scroll down C/Y = 12, press 360 as N, Press 7 as I/Y

Press -20000 as PV, press 0 as FV

Now, press CPT + PMT, you will get EMI

For repayment schedule, type the -EMI value = - 6653.02 and press PMT ,

Press 2nd and PV button, it will show P1 = press 60 , press enter and scroll down P2 = 60, press enter

Scroll down you will get the remaining balance

Use this balance as given above.