Loan Composite Amount rates used 360000 4.75 1425 12/360000*100 Initial ΕΟΥ1 6.75 ΕΟΥ2 8.75 EOY3 9.75 Price 450000 %down 20% Annual Cap 2% Life Cap. 5% Loan Amt. Price 450000 () DP 90000 Loan 360000
Years 30 Months Payment Interest balance amount 360000 Step 1 1 1877.93 1425 452.93 359547.07 2 1877.93 1423.21 454.72 359092.35 To calculate instalment use function "PMT" 1877.93 1421.41 456.52 358635.83 -(+PMT(4.75% / 12,360 ,360000) you can use this function with "Round" function t get 2 digits value from decimal 458.33 358177.50 4 1877.93 1419.6 1417.79 5 1877.93 460.14 357717.36 ROUND(+PMT (4.75% / 12,360 ,360000), 2) 6 1877.93 1415.96 461.97 357255.39 7 1877.93 1414.14 463.79 35679160 where 4.75 % is the initial annual rate, 360 is the no. of total instalment and 360000 is the loan amount. 1877.93 1412.3 465.63 356325.97 467.47 355858.50 9 1877,93 1410.46 1408.61 10 1877.93 469,32 355389,18 We devide rate by 12 to get the monthly rate 1406.75 11 1877.93 471.18 354918.00 1877.93 1404.88 12 473.05 354444.95 2323.69 1993.75 329.94 354115.01 13 Step- 2 331.79 353783.22 14 2323.69 1991.90 When we reach at the 13th month, no. of instalment remain 348 and the rate will 333.66 353449,56 increase to 4.75 % +296 (annual capatalization) 6.75 % 15 2323.69 1990.03 335.54 353114.02. 337.42 352776.60 339.32 352437.28 341.23 352096.05 1988.15 16 2323.69 17 2323.69 1986.27 To calculate further 12 instalment use function "PMT" 18 2323.69 1984.37 ROUND(+PMT (6.75% /12,348, 354444.95),2) 19 2323.69 1982.46 343,15 351752.90 20 2323.69 1980.54 where 6.75 % is the EOY1 annual rate, 348 is the no. of total instalment and 354444.95 345.08 351407.82 347.02 351060.80 348.97 350711.83 350.94 350360.89 is the remain ing loan amount. 21 2323.69 1978.61 22 2323.69 1976.67 23 2323.69 1974.72 We devide rate by 12 to get the monthly rate 2323.69 24 1972.75 co st
2798.35 243.64 350117.25 25 2554,71 Step-3 26 2798.35 2552.94 245.41 349871.84 When we reach at the 25th month, no. of instalment remain 336 and the rate will increase to 6.75%+29% (an nual capatalization) = 8.75 % 27 2798.35 2551.15 247.20 349624.64 28 2798.35 2549,35 249.00 349375.64 29 2798.35 2547.53 250.82 349124.82 To calculate further 12 instalment use function "PMT" -ROUND(+PMT(8.75 30 2798.35 2545.70 252.65 348872.17 % / 12,336 , 350360.89),2) 2543.86 254,49 348617.68 31 2798.35 2542.00 32 2798.35 256.35 348361.33 where 8.75 % is the EOY2 annual rate, 336 is the no, of total instalment and 350360.89 33 2798.35 2540,13 258.22 348103,11 is the remaining loan amount. 34 2798.35 2538.25 260.10 347843.01 2798.35 35 2536.36 261.99 347581.02 We devide rate by 12 to get the monthly rate 263.91 347317.11 36 2798.35 2534.44 3043.08 2821.95 37 221.13 347095.98 Step-4 38 3043.08 2820.15 222.93 346873.05 When we reach at the 37th month, no, of instalment remain 324 and the rate will increase to 6.75 %+2 % (annual capatalization subject to 5% life Cap(4.75+59 %)) = 9.75% 39 3043.08 2818.34 224.74 346648.31 40 3043.08 226.56 34642175 2816.52 41 3043.08 2814.68 228.40 346193,35 To calculate further 324 instalment 42 use function "PMT" 3043.08 2812.82 230.26 345963.09 % / 12,324,347317.11),2) 4- 232.13 345730.96 -ROUND(+PMT(9.75 3043.08 2810.95 44 3043.08 2809.06 234.02 345496.94 where 9.75 % is the EOY3 annual rate, 324 is the no. of total instalment and 347317.11 45 3043.08 2807.16 235.92 345261.02 is the remaining loan amount. 3043.08 2805.25 237.83 345023.19 47 3043.08 2803.31 239.77 344783.42 We devide rate by 12 to get the monthly rate 49 3043.08 2801.37 241.71 344541.71 3043.08 2799.40 243.68 344298.03 49 50 3043.08 2797.42 245.66 344052.37 3043.08 247,65 343804.72 51 2795.43 2793.41 52 3043.08 249.67 343555.05 3043.08 2791.38 251.70 343303.35 3043.08 54 2789.34 253.74 343049,61 55 3043.08 2787.28 255.80 342793.81
56 3043.08 257.88 342535.93 2785.20 57 3043.08 2783.10 259.98 342275.95 58 3043.08 2780,99 262.09 342013.86 3043.08 2778 8G 59 264.22 341749.64 3043.08 2776.72 266.36 341483.28 268.53 3412 14,75 61 3043.08 2774.55 62 3043.08 2772.37 270.71 340944.04 3043.08 2770.17 272.91 340671.13 3043.08 2767.95 275.13 340396.00 277.36 340118.64 65 3043.08 2765.72 3043.08 2763.46 279.62 339839.02 281.89 339557.13 3043.08 2761.19 3043.08 2758.90 284.18 339272.95 69 3043.08 2756.59 286.49 338986.46 70 3043.08 2754.26 288.82 338697.64 71 3043.08 2751.92 291.16 338406.48. 72 3043.08 2749.55 293.53 33811295 73 3043.08 2747.17 295.91 337817.04 298.32 3375 18.72 74 3043.08 2744.76 75 3043.08 2742.34 300.74 3372 17.98 76 3043.08 2739.90 303.18 33691480 77 3043.08 2737.43 305.65 336609.15 3043.08 2734.95 78 308.13 336301.02 79 3043.08 2732.45 310.63 335990.39 80 3043.08 2729.92 313.16 335677.23 81 3043.08 2727.38 315.70 335361,53 82 3043.08 2724.81 318.27 335043.26 3043.08 2722.23 83 32085 334722.41 84 3043.08 2719.62 323.46 334398.95 85 3043.08 2716.99 326,09 334072.86 86 3043.08 2714.34 328.74 333744.12 3043.08 2711.67 331.41 333412,71 3043.08 2708.98 334.10 333078.61 3043.08 2706.26 336.82 332741.79 339.55 332402.24 90 3043.08 2703.53 91 3043.08 2700.77 342.31 33205993 345,09 33171484 92 3043.08 2097.99 3043.08 2695.18 93 347.90 331366.94 94 3043.08 2692.36 350,72 331016.22 3043.08 95 2689.51 353.57 330662.65 356.45 330306.20 96 3043.08 2686.63 97 3043.08 2683.74 359.34 329946.86 98 3043.08 2680.82 362.26 329584.60 99 3043.08 267787 365.21 329219.39 100 3043.08 2674.91 368.17 328851.22
371.16 328480,06 101 3043.08 2671.92 102 3043.08 2668.90 374.18 328105.88 103 3043.08 2665 86 377.22 327728.66 104 3043.08 2662.80 380.28 327348.38 3043.08 105 2659.71 383.37 326965.01 106 3043.08 2656.59 386.49 326578.52 107 3043.08 2653.45 389.63 326188.89 108 3043.08 2650.28 392.80 325796.09 395.99 325400.10 109 3043.08 2647.09 110 3043.08 2643.88 399.20 325000.90 402.45 324598.45 111 3043.08 2640.63 112 3043.08 2637.36 405.72 324192.73 409.01 323783.72 113 3043.08 2634.07 114 3043.08 2630.74 412.34 323371.38 415.09 322955.69 115 3043.08 2627.39 116 3043.08 2624.01 419.07 322536.62 117 3043.08 2620.61 422.47 322114.15 113 3043.08 2617.18 425.90 321688.25 119 3043.08 2613.72 429.36 321258.89 120 3043.08 2610.23 432.85 320826.04 121 3043.08 2606.71 436.37 320389.67 122 3043.08 2603.17 439.91 319949.76 123 3043.08 2599.59 443.49 319506.27 2595.99 124 3043.08 447.09 319059.18 450.72 318608.46 125 3043.08 2592.36 2588.G9 454,39 318154.07 126 3043.08 127 3043.08 2585.00 458.08 317695.99 128 3043.08 2581.28 461 80 317234.19 3043.08 2577.53 129 465.55 316768.64 469.33 316299.31 473.15 3158 26.16 130 3043.08 2573.75 131 3043.08 2569.93 476,99 315349.17 132 3043.08 2566.09 133 3043.08 2562.21 480.87 314868.30 3043.08 2558.30 134 484.78 314383.52 488.71 313894.81 135 3043.08 2554.37 2550.40 136 3043.08 492.68 313402.13 137 3043.08 2546.39 496.09 312905.44 500.72 312404.72 138 3043.08 2542.36 139 3043.08 2538.29 504.79 311899.93 140 3043.08 2534.19 508.89 31139104 513.03 310878.01 141 3043.08 2530.05 142 3043.08 2525 88 517.20 31036081 143 3043.08 2521.68 521.40 309839.41 525,63 309313.78 144 3043.08 2517.45 145 3043.08 2513.17 529.91 308783.87 146 3043.08 2508.87 534.21 308249.66 147 3043.08 2504.53 538.55 307711.11
148 3043.08 2500.15 542.93 307168.18 149 3043.08 2495.74 547.34 306620.84 150 3043.08 2491.29 551.79 306069.05 151 3043.08 2486.81 556.27 305512.78 2482.29 152 3043.08 560.79 30495199 153 3043.08 2477.73 565.35 304386.64 2473.14 154 3043.08 569.94 3038 16.70 155 3043.08 2468.51 574,57 303242.13 156 3043.08 2463.84 579.24 302662.89 157 3043.08 2459.14 583.94 302078.95 158 3043.08 2454.39 588.9 301490.26 159 3043.08 2449.61 593.47 300896.79 160 3043.08 2444,9 598.29 300298.50 161 3043.08 2439.93 G03.15 299695.35 608.06 299087.29 162 3043.08 2435.02 613.00 298474.29 163 3043.08 2430.08 164 3043.08 2425.10 617.98 297856.31 623.00 2972 33.31 165 3043.08 2420,08 166 3043.08 2415.02 628.06 296605.25 167 3043.08 2409.92 633.16 295972.09 168 3043.08 2404,77 638.31 295333.78 169 3043.08 2399.59 643.49 294690.29 170 3043.08 2394.36 648.72 294041.57 3043.08 2389.09 171 653.99 293387.58 172 3043.08 2383.77 659.31 292728.27 173 3043.08 2378.42 G64.G6 292063.61 174 3043.08 2373.02 670.06 291393.55 675.51 290718.04 175 3043.08 2367.57 176 3043.08 2362,08 681.00 290037.04 177 3043.08 2356.55 686.53 289350.51 178 3043.08 2350.97 692.11 288658.40 179 3043.08 2345.35 697.73 287960.67 180 3043.08 2339.68 703.40 287257.27 3043.08 181 2333.97 709.11 28654a,16 132 3043.08 2328.20 714.88 285833.28 183 720,68 285112.60 3043.08 2322.40 184 3043.08 2316.54 726,54 284386.06 185 3043.08 2310.64 732.44 283653.62 186 3043.08 2304.09 738.39 282915.23 744,39 282170.84 187 3043.08 2298.G9 188 3043.08 2292.64 750.44 281420.40 756.54 280663.86 189 3043.08 2286.54 190 3043.08 2280.39 762.69 279901.17
191 3043.08 2274.20 768 88 279132.29 2267.95 192 3043.08 775.13 278357.16 193 3043.08 2261.65 781.43 277575,73 194 3043.08 2255.30 787.78 276787.95 794.18 275993.77 195 3043.08 2248.90 196 3043.08 2242.45 800.63 275193.14 3043.08 197 2235 94 807.14 274386.00 198 3043.08 2229.39 813.9 273572.31 820.30 272752.01 199 3043.08 2222.78 200 3043.08 2216.11 826.97 271925.04 201 3043.08 2209.39 833.9 271091.35 2202.62 202 3043.08 840.46 270250.89 203 3043.08 2195.79 847.29 269403.60 854.18 26854942 204 3043.08 2188.90 205 3043.08 2181.96 861.12 267688.30 868.11 2668 20.19 206 3043.08 2174.97 3043.08 2167.91 207 875.17 265945.02 208 3043.08 216080 882.28 265062,74 209 3043.08 2153.63 889.45 264173.29 896.67 263276.62 210 3043.08 2146.41 211 3043.08 2139.12 903.96 262372.66 212 3043.08 2131.78 911.30 261461.36 213 3043.08 2124.37 918.71 260542.65 214 3043.08 2116.91 926.17 259616.48 215 3043.08 2109.38 933.20 258682,78 941.28 257741.50 216 3043.08 2101.30 217 3043.08 2094.15 948.93 256792.57 218. 3043.08 2086.44 956.64 255835.93 219 3043.08 2078.67 964.41 254871.52 220 3043.08 2070.83 972.25 253899.27 221 3043.08 2062.93 980.15 252919.12 222 3043.08 2054.97 988.11 251931.01 223 3043.08 2046.94 996.14 25093487 224. 3043.08 2038 85 1004.23 249930.64 225 3043.08 2030.9 1012.39 248918.25 3043.08 2022 4G 226 1020.62 247897.63 227 3043.08 2014.17 1028.91 24686872 228 3043.08 2005.81 1037.27 245831.45 229 3043.08 1997.38 1045.70 244785.75 230 3043.08 1988.88 1054.20 243731.55 1062.76 2426G8.79 231 3043.08 1980.32 232 3043.08 1971.68 1071.40 241597.39 233 3043.08 1962.98 1080.10 240517.29 234 3043.08 1954,20 1088.88 239428.41 235 3043.08 1945.36 1097.72 238330.69 236 3043.08 1936.44 1106.64 237224.05
237 3043.08 1927.45 1115.63 236108.42 238 3043.08 1918.38 1124,70 234983.72 3043.08 1909.24 239 1133 84 233849.88 1143.05 232706.83 240 3043.08 1900,03 241 3043.08 1890.74 1152.34 23155449 242 3043.08 1881.38 1161.70 230392.79 1171.14 2292 21.65 243 3043.08 1871.94 3043.08 244 1862.43 1130,65 228041.00 1190.25 226850.75 245 3043.08 1852 83 246 3043.08 1843.16 1199.92 225650.83 247 3043.08 1833.41 1209.67 224441.16 1219.50 2232 21.66 248 3043.08 1823.58 3043.08 1813.68 249 1229.40 221992.26 250 3043.08 1803.09 1239.39 220752.87 251. 3043.08 1793.62 1249.46 21950341 252 3043.08 1783.47 1259.61 218243.80 253 3043.08 1773.23 1209.85 216973.95 3043.08 254 1762.91 1280.17 215693,78 255 3043.08 1752.51 1290.57 214403.21 256 3043.08 174203 1301.05 213102.16 257 3043.08 1731.46 1311.62 211790,54 258 1720 80 3043.08 1322 .28 210468.26 259 3043.08 1710.05 1333.03 209135.23 260 3043.08 1699.22 1343.86 207791.37 261 3043.08 1688.30 1354.78 206436.59 262 3043.08 1677.30 1365.78 20507081 3043.08 1666.20 263 1376.88 203693.93 264 3043.08 1655.01 1388.07 202305.86 265 3043.08 1643.74 1399.34 200906.52 1410.71 199495.81 266 3043.08 1632.37 267 3043.08 1620.90 1422.18 198073.63 3043.08 1609.35 268 1433.73 196639.90 1445.38 195194.52 269 3043.08 1597.70 270 3043.08 1585.96 1457.12 193737.40 1468.96 192268.44 271 3043.08 1574.12 272 3043.08 1562.18 1480,90 19078754 273 3043.08 1550.15 1492.93 189294.61 274 3043.08 1538.02 1505.06 187789.55 1525.79 275 3043.08 1517.29 186272.26 276 3043.08 1513.46 1529.62 184742.64 277 3043.08 1501.03 1542.05 183200.59 278 3043.08 1488.50 1554.58 181646.01 279 3043.08 1475.87 1567.21 180078.80 1463.14 280 3043.08 1579.94 178498.86 281. 3043.08 1450.30 1592.78 176906.08 282 3043.08 1437.36 1605.72 175300.36 283 3043.08 1424.32 1618.76 173681.60
3043.08 284 1411.16 1631.92 172049.68 285 3043.08 1397.90 1645.18 170404.50 286 3043.08 1384.54 1658.54 168745.96 1672.02 167073.94 287 3043.08 1371.06 288 3043.08 1357.48 1685.60 165388.34 3043.08 1343.78 289 1699.30 163689.04 290 3043.08 1329,97 1713.11 161975.93 1727.03 1602 48.90 291 3043.08 1316.05 292 3043.08 1302.02 1741.06 158507.84 293 3043.08 1287 88 1755.20 156752.64 3043.08 1769.46 154983.18 1783.84 153199.34 294 1273.62 295 3043.08 1259.24 296 3043.08 1244,74 1798.34 151401.00 297 3043.08 1230.13 1812.95 149588.05 1827,68 147760.37 298. 3043.08 1215.40 3043.08 299 1200.55 1842.53 145917.84 1857.50 1440G0.34 300 3043.08 1185.58 301 3043.08 1170.49 1872.59 142187.75 302 3043.08 1155.28 1887.30 140299.95 303 3043.08 1139.94 1903.14 13839681 1918.61 136478.20 304 3043.08 1124.47 305 3043.08 1108.89 1934.19 134544.01 1949.91 132594.10 306 3043.08 1093.17 307 3043.08 1077.33 1965.75 130628.35 1981.72 129G4G.G3 308 3043.08 1061.36 309 3043.08 1045.25 1997.83 126648.80 310 3043.08 1029.02 2014.06 124634,74 311 3043.08 1012.66 2030.42 12260432 312 3043.08 996.16 2046.92 120557.40 313 3043.08 979.53 2063.55 118493.85 314 3043.08 962.76 2080.32 116413.53 315 3043.08 945.86 2097.22 114316.31 316 3043.08 928.82 2114.26 112202.05 317 3043.08 911.64 2131.44 110070.61 318 3043.08 894.32 2148.76 107921.85 319 3043.08 876.87 2166.21 105755.64 320 3043.08 859.26 2183.82 10357182 2201.56 1013 70.26 321 3043.08 841.52 322 3043.08 823.63 2219.45 99150.81 323 3043.08 805.60 2237.48 969 13.33 324 3043.08 78742 2255.66 94657.67
325 3043.08 769.09 2273.99 92383.68 3043.08 326 750.62 2292.46 90091.22 327 3043.08 73199 2311.09 8770.13 328 3043.08 713.21 2329 87 s5450.26 329 3043.08 694.28 2348.0 8310146 330 3043.08 675.20 236788 a073358 3043.08 331 G55.96 2387.12 7834646 332 3043.08 G3656 2406.52 75939.94 333 3043.08 617.01 2426.07 7351387 3043.08 334 597.30 2445.78 71068.09 335 3043.08 577.43 2465.65 G0244 336 3043.08 557.39 2485.9 G6116.75 537.20 2505 337 3043.08 G361087 338 3043.08 516.84 2526.24 G1084.63 339 3043.08 49631 2546.77 s853786 340 3043.08 475.62 2567.4 55970.40 341 3043.08 454.76 2588.32 53382.08 3043.08 342 433.73 2009.35 s0772.73 3043.08 343 412.53 2630.55 48142.18 3043.08 344 391.16 265192 45490.26 345 3043.08 369.61 2673.47 42816.79 346 3043.08 34789 2095.19 4012160 347 3043.08 325.99 2717.09 3740451 348 3043.08 30391 2739.17 3466534 3043.08 349 28166 276142 3190392 350 3043.08 259.22 2783 8 2912006 351 3043.08 236.0 2806.48 2631358 352 3043.08 21380 2829.28 2348430 3043.08 353 190.81 2852.27 20632.03 354 3043.08 167.64 2875.44 1775659 3043.08 355 144.27 2898 81 14857.78 3043.08 356 120.72 2922.36 11935.42 357 3043.08 96.98 2946.10 8989.32 358 3043.08 73.04 2970.04 G019.28 359 3043.08 48.91 2994.17 3025.11 360 3043.08 24.58 3018.50 661 This difference is due to round off
K M N 1 2 3 4 Balance Monthly IRR Annual IRR Date 5 360000 31-12-2018 360000 98%-22535.16 94% 6 -1877.93 31-01-2019 7 -1877.93 28-02-2019 to calculate monthly IRR 8 -1877.93 31-03-2019 use XIRR function -1877.93 30-04-2019 XIRR (K5:K17,L5:L17) 10 -1877.93 31-05-2019 11 -1877.93 30-06-2019 to calculate IRR 12 -1877.93 31-07-2019 use IRR function 13 -1877.93 31-08-2019 =+IRR(05:06) 14 -1877.93 30-09-2019 15 -1877.93 31-10-2019 16-1877.93 30-11-2019 17 -1877.93 31-12-2019
K P 19 354444.95 31-12-2019 354444.95 202323.69 31-01-2020 98%-27884.28 92% 21 2323.69 29-02-2020 to calculate monthly IRR 22 2323.69 31-03-2020 232323.69 30-04-2020 use XIRR function XIRR(K19:K31,L19:L31) 24 -2323.69 31-05-2020 25 -2323.69 30-06-2020 to calculate IRR 26 -2323.69 31-07-2020 use IRR function 27 -2323.69 31-08-2020 +IRR(019:020) 28 -2323.69 30-09-2020 29 -2323.69 31-10-2020 302323.69 30-11-2020 31-2323.69 31-12-2020 NM
K M N 32 33 350360.89 31-12-2020 350360.89 -97% -33580.2 -90% 34 -2798.35 31-01-2021 35 -2798.35 28-02-2021 to calculate monthly IRR 36 -2798.35 31-03-2021 372798.35 30-04-2021 382798.35 31-05-2021 use XIRR function =XIRR(K33:K45,L33:L45) 39 -2798.35 30-06-2021 to calculate IRR 40 2798.35 31-07-2021 use IRR function 41 2798.35 31-08-2021 422798.35 30-09-2021 =+IRR(033:034) 43 -2798.35 31-10-2021 44 -2798.35 30-11-2021 45 -2798.35 31-12-2021 46 O.
K M N 46 47 347317.11 31-12-2021 347317.11 97% -36516.96 -89% 48 -3043.08 31-01-2022 49 -3043.08 28-02-2022 to calculate monthly IRR 50 -3043.08 31-03-2022 use XIRR function 51 3043.08 30-04-2022 =XIRR(K47:K59, L47:L59) 52 3043.08 31-05-2022 53 3043.08 30-06-2022 to calculate IRR 54 3043.08 31-07-2022 55-3043.08 31-08-2022 56-3043.08 30-09-2022 573043.08 31-10-2022 58-3043.08 30-11-2022 593043.08 31-12-2022 use IRR function +IRR(047:048) 60 61 O