| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS
Inputs Summary
Loan Amount $250,573.00 Annual Interest rate 5.09% Lump sum $50.00 Term of loan in years 34.83 Starting 1 03/07/2008 Payment Frequency Monthly Ending 12 02/07/2009 Closing date 03/07/2008 Interval 0 Payment (per period) $1,270.51 Amortization Schedule No. Due Date Payment Due Principal Additional Payment Interest Balance $250,573.00 1 03/07/2008 1,270.51 218.76 50.00 1,051.75 250,304.24 2 04/07/2008 1,270.51 219.89 50.00 1,050.62 250,034.35 3 05/07/2008 1,270.51 221.02 50.00 1,049.49 249,763.33 4 06/07/2008 1,270.51 222.16 50.00 1,048.35 249,491.17 5 07/07/2008 1,270.51 223.30 50.00 1,047.21 249,217.87 6 08/07/2008 1,270.51 224.45 50.00 1,046.06 248,943.42 7 09/07/2008 1,270.51 225.60 50.00 1,044.91 248,667.82 8 10/07/2008 1,270.51 226.76 50.00 1,043.75 248,391.06 9 11/07/2008 1,270.51 227.92 50.00 1,042.59 248,113.14 10 12/07/2008 1,270.51 229.09 50.00 1,041.42 247,834.05 11 01/07/2009 1,270.51 230.26 50.00 1,040.25 247,553.79 12 02/07/2009 1,270.51 231.43 50.00 1,039.08 247,272.36 13 03/07/2009 1,270.51 232.62 0.00 1,037.89 247,039.74 14 04/07/2009 1,270.51 233.59 0.00 1,036.92 246,806.15 WHAT FORMULA SHOULD I PUT IN MY ADDITIONAL PAYMENT SO THAT I CAN PLAY WITH DIFFERENT INTERVALS. FOR EXAMPLE, AN EXTRA PMT EVERY 4 MONTHS STARTING ON THE 6 MONTH AND ENDING ON THE 24 MONTHS. THANKS, JAGAUDE |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS
My first suggestion is to forget about additional payments, and modify the
regular payment. So instead of trying to calculate the effect of an extra $150 every three months, use an extra $50 every month. This will get you a result that is close enough for almost every circumstance. If you insist on irregular addtional payments, my next recommendation is to enter them manually. In your example, you want 'an extra amt every 4th month starting on the 6th month and ending on the 24th month.' So just enter those numbers in your table. It won't take you more than a minute. If you want to make data entry a little easier, enter your payment pattern (eg: 100, 0, 0, 0). Now highlight those four cells, and drag them down as far as you want. Excel will fill in that pattern for all cells you drag to. If you really want a programmatic solution, you need to use the Mod function. So for every 4th month, you want mod(month,4)=0. So create an If statement like: =if(mod(month(f2),4)=0,$ExtraPayment,0) and copy it down your column. This will only work if 12 can be divided by your interval (eg, 2, 3, 4, 6 and 12 month intervals). Regards, Fred. "Jagaude" wrote in message ... Inputs Summary Loan Amount $250,573.00 Annual Interest rate 5.09% Lump sum $50.00 Term of loan in years 34.83 Starting 1 03/07/2008 Payment Frequency Monthly Ending 12 02/07/2009 Closing date 03/07/2008 Interval 0 Payment (per period) $1,270.51 Amortization Schedule No. Due Date Payment Due Principal Additional Payment Interest Balance $250,573.00 1 03/07/2008 1,270.51 218.76 50.00 1,051.75 250,304.24 2 04/07/2008 1,270.51 219.89 50.00 1,050.62 250,034.35 3 05/07/2008 1,270.51 221.02 50.00 1,049.49 249,763.33 4 06/07/2008 1,270.51 222.16 50.00 1,048.35 249,491.17 5 07/07/2008 1,270.51 223.30 50.00 1,047.21 249,217.87 6 08/07/2008 1,270.51 224.45 50.00 1,046.06 248,943.42 7 09/07/2008 1,270.51 225.60 50.00 1,044.91 248,667.82 8 10/07/2008 1,270.51 226.76 50.00 1,043.75 248,391.06 9 11/07/2008 1,270.51 227.92 50.00 1,042.59 248,113.14 10 12/07/2008 1,270.51 229.09 50.00 1,041.42 247,834.05 11 01/07/2009 1,270.51 230.26 50.00 1,040.25 247,553.79 12 02/07/2009 1,270.51 231.43 50.00 1,039.08 247,272.36 13 03/07/2009 1,270.51 232.62 0.00 1,037.89 247,039.74 14 04/07/2009 1,270.51 233.59 0.00 1,036.92 246,806.15 WHAT FORMULA SHOULD I PUT IN MY ADDITIONAL PAYMENT SO THAT I CAN PLAY WITH DIFFERENT INTERVALS. FOR EXAMPLE, AN EXTRA PMT EVERY 4 MONTHS STARTING ON THE 6 MONTH AND ENDING ON THE 24 MONTHS. THANKS, JAGAUDE |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS
Extra Payment Mortgage Calculator
http://www.vertex42.com/ExcelTemplat...-payments.html Other good samples he http://www.vertex42.com/ExcelTemplat...readsheet.html Regards, Ryan--- -- RyGuy "Fred Smith" wrote: My first suggestion is to forget about additional payments, and modify the regular payment. So instead of trying to calculate the effect of an extra $150 every three months, use an extra $50 every month. This will get you a result that is close enough for almost every circumstance. If you insist on irregular addtional payments, my next recommendation is to enter them manually. In your example, you want 'an extra amt every 4th month starting on the 6th month and ending on the 24th month.' So just enter those numbers in your table. It won't take you more than a minute. If you want to make data entry a little easier, enter your payment pattern (eg: 100, 0, 0, 0). Now highlight those four cells, and drag them down as far as you want. Excel will fill in that pattern for all cells you drag to. If you really want a programmatic solution, you need to use the Mod function. So for every 4th month, you want mod(month,4)=0. So create an If statement like: =if(mod(month(f2),4)=0,$ExtraPayment,0) and copy it down your column. This will only work if 12 can be divided by your interval (eg, 2, 3, 4, 6 and 12 month intervals). Regards, Fred. "Jagaude" wrote in message ... Inputs Summary Loan Amount $250,573.00 Annual Interest rate 5.09% Lump sum $50.00 Term of loan in years 34.83 Starting 1 03/07/2008 Payment Frequency Monthly Ending 12 02/07/2009 Closing date 03/07/2008 Interval 0 Payment (per period) $1,270.51 Amortization Schedule No. Due Date Payment Due Principal Additional Payment Interest Balance $250,573.00 1 03/07/2008 1,270.51 218.76 50.00 1,051.75 250,304.24 2 04/07/2008 1,270.51 219.89 50.00 1,050.62 250,034.35 3 05/07/2008 1,270.51 221.02 50.00 1,049.49 249,763.33 4 06/07/2008 1,270.51 222.16 50.00 1,048.35 249,491.17 5 07/07/2008 1,270.51 223.30 50.00 1,047.21 249,217.87 6 08/07/2008 1,270.51 224.45 50.00 1,046.06 248,943.42 7 09/07/2008 1,270.51 225.60 50.00 1,044.91 248,667.82 8 10/07/2008 1,270.51 226.76 50.00 1,043.75 248,391.06 9 11/07/2008 1,270.51 227.92 50.00 1,042.59 248,113.14 10 12/07/2008 1,270.51 229.09 50.00 1,041.42 247,834.05 11 01/07/2009 1,270.51 230.26 50.00 1,040.25 247,553.79 12 02/07/2009 1,270.51 231.43 50.00 1,039.08 247,272.36 13 03/07/2009 1,270.51 232.62 0.00 1,037.89 247,039.74 14 04/07/2009 1,270.51 233.59 0.00 1,036.92 246,806.15 WHAT FORMULA SHOULD I PUT IN MY ADDITIONAL PAYMENT SO THAT I CAN PLAY WITH DIFFERENT INTERVALS. FOR EXAMPLE, AN EXTRA PMT EVERY 4 MONTHS STARTING ON THE 6 MONTH AND ENDING ON THE 24 MONTHS. THANKS, JAGAUDE |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS
On Mar 19, 3:36*pm, ryguy7272
wrote: Extra Payment Mortgage Calculatorhttp://www.vertex42.com/ExcelTemplates/extra-payments.html Other good samples hehttp://www.vertex42.com/ExcelTemplat...st-spreadsheet.... Regards, Ryan--- -- RyGuy "Fred Smith" wrote: My first suggestion is to forget about additional payments, and modify the regular payment. So instead of trying to calculate the effect of an extra $150 every three months, use an extra $50 every month. This will get you a result that is close enough for almost every circumstance. If you insist on irregular addtional payments, my next recommendation is to enter them manually. In your example, you want 'an extra amt every 4th month starting on the 6th month and ending on the 24th month.' So just enter those numbers in your table. It won't take you more than a minute. If you want to make data entry a little easier, enter your payment pattern (eg: 100, 0, 0, 0). Now highlight those four cells, and drag them down as far as you want. Excel will fill in that pattern for all cells you drag to. If you really want a programmatic solution, you need to use the Mod function. So for every 4th month, you want mod(month,4)=0. So create an If statement like: =if(mod(month(f2),4)=0,$ExtraPayment,0) and copy it down your column. This will only work if 12 can be divided by your interval (eg, 2, 3, 4, 6 and 12 month intervals). Regards, Fred. "Jagaude" wrote in message ... Inputs Summary Loan Amount $250,573.00 Annual Interest rate 5.09% Lump sum $50.00 Term of loan in years 34.83 Starting 1 03/07/2008 Payment Frequency Monthly Ending 12 02/07/2009 Closing date 03/07/2008 Interval 0 Payment (per period) $1,270.51 Amortization Schedule No. Due Date * * * * Payment Due Principal Additional Payment Interest Balance $250,573.00 1 03/07/2008 1,270.51 218.76 50.00 1,051.75 250,304.24 2 04/07/2008 1,270.51 219.89 50.00 1,050.62 250,034.35 3 05/07/2008 1,270.51 221.02 50.00 1,049.49 249,763.33 4 06/07/2008 1,270.51 222.16 50.00 1,048.35 249,491.17 5 07/07/2008 1,270.51 223.30 50.00 1,047.21 249,217.87 6 08/07/2008 1,270.51 224.45 50.00 1,046.06 248,943.42 7 09/07/2008 1,270.51 225.60 50.00 1,044.91 248,667.82 8 10/07/2008 1,270.51 226.76 50.00 1,043.75 248,391.06 9 11/07/2008 1,270.51 227.92 50.00 1,042.59 248,113.14 10 12/07/2008 1,270.51 229.09 50.00 1,041.42 247,834.05 11 01/07/2009 1,270.51 230.26 50.00 1,040.25 247,553.79 12 02/07/2009 1,270.51 231.43 50.00 1,039.08 247,272.36 13 03/07/2009 1,270.51 232.62 0.00 1,037.89 247,039.74 14 04/07/2009 1,270.51 233.59 0.00 1,036.92 246,806.15 WHAT FORMULA SHOULD I PUT IN MY ADDITIONAL PAYMENT SO THAT I CAN PLAY WITH DIFFERENT INTERVALS. FOR EXAMPLE, AN EXTRA PMT EVERY 4 MONTHS STARTING ON THE 6 MONTH AND ENDING ON THE 24 MONTHS. THANKS, JAGAUDE- Hide quoted text - - Show quoted text - Thanks guys. I used the MOD fonction and it works good. Cheers, Jagaude |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mortgage amortization schedule with Interval Extra Payments | Excel Worksheet Functions | |||
| Mortgage calculation after a large extra payment | Excel Discussion (Misc queries) | |||
| Mortgage calculation after a large extra payment | Excel Worksheet Functions | |||
| need template on mortgage loan with extra paymts where can i find | Charts and Charting in Excel | |||
| Formula for int. paid when extra monthly pmts are made. | Excel Discussion (Misc queries) | |||
Linear Mode
