Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
I am trying to use Excel to calculate my mortgage ammortization
schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
I'm going to ask what may be a silly question, but have you seen the free
Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page: http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page: http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: I'm going to ask what may be a silly question, but have you seen the free Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM- Hide quoted text - - Show quoted text - The one you pointed me to I had figured out, there was a seperate one for an ammortization schedule, but it does not show formulas, and when I added extra principal payments, the sheet did not recalculate. The online sheet shows forumulas. When copied to an existing sheet, it does not copy forumulas. What gives? jIM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
On Feb 16, 11:00 am, "jIM" wrote:
On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I'm going to ask what may be a silly question, but have you seen the free Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM- Hide quoted text - - Show quoted text - The one you pointed me to I had figured out, there was a seperate one for an ammortization schedule, but it does not show formulas, and when I added extra principal payments, the sheet did not recalculate. The online sheet shows forumulas. When copied to an existing sheet, it does not copy forumulas. What gives? jIM- Hide quoted text - - Show quoted text - I had two sessions of excel running, it would not copy with formulas to a different session. Closed second session, opened existing workbook in current session, and copy worked with formulas. The templates "fixed" the problem, but they are way too automated (for copying and if-then analysis). I like doing a payment schedule, then highlighting in yellow the row where the payoff occurs. Then doing another analysis and highlighting that payoff row in yellow, comparing the two different schedules. The sheet allows me to do ONE analysis. Once I copy the whole structure I need to backtrack and find mistakes (the copied cells did not update when I changed the payment structure). for example, is it better to make two $625 payments in November and December of each year, or one $1250 payment in December. I know the two payments are better (based on rules of compounding), but I want to see the exact difference (in months). If payment was $2500, what is the return on paying the additional $1250 each year? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
Has the template been any help in tracking down your problem in your
workbook? Or do you still need assistance with it? You said it wasn't updating automatically, have you checked Tools | Options | [Calculation] tab to make sure it is still set to Automatic calculating? If you recently opened a workbook with it set to manual, it could have gotten toggled. If you think there might be something I could help with but would need the workbook to dig into, feel free to email as attachment to (remove spaces) HelpFrom @ jlathamsite. com "jIM" wrote: On Feb 16, 11:00 am, "jIM" wrote: On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I'm going to ask what may be a silly question, but have you seen the free Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM- Hide quoted text - - Show quoted text - The one you pointed me to I had figured out, there was a seperate one for an ammortization schedule, but it does not show formulas, and when I added extra principal payments, the sheet did not recalculate. The online sheet shows forumulas. When copied to an existing sheet, it does not copy forumulas. What gives? jIM- Hide quoted text - - Show quoted text - I had two sessions of excel running, it would not copy with formulas to a different session. Closed second session, opened existing workbook in current session, and copy worked with formulas. The templates "fixed" the problem, but they are way too automated (for copying and if-then analysis). I like doing a payment schedule, then highlighting in yellow the row where the payoff occurs. Then doing another analysis and highlighting that payoff row in yellow, comparing the two different schedules. The sheet allows me to do ONE analysis. Once I copy the whole structure I need to backtrack and find mistakes (the copied cells did not update when I changed the payment structure). for example, is it better to make two $625 payments in November and December of each year, or one $1250 payment in December. I know the two payments are better (based on rules of compounding), but I want to see the exact difference (in months). If payment was $2500, what is the return on paying the additional $1250 each year? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
On Feb 16, 6:49 am, "jIM" wrote:
D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) I am not sure I posted my previous response correctly. To be brief, your primary error is using C6 (remaining balance) instead of $B$2 (loan amount) in ISPMT(). (In your design, $B$2 contains the negative loan amount.) However, I believe that even with that correction, ISPMT() computes the wrong amount. I don't know why, off-hand. But the better solution is to avoid ISPMT() altogether. Why use a sledgehammer to kill ant? The period interest can be computed simply by: =C6*$C$2/12 where $C$2 is the annual interest rate in your design. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
On Feb 16, 12:08 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: Has the template been any help in tracking down your problem in your workbook? Or do you still need assistance with it? You said it wasn't updating automatically, have you checked Tools | Options | [Calculation] tab to make sure it is still set to Automatic calculating? If you recently opened a workbook with it set to manual, it could have gotten toggled. If you think there might be something I could help with but would need the workbook to dig into, feel free to email as attachment to (remove spaces) HelpFrom @ jlathamsite. com "jIM" wrote: On Feb 16, 11:00 am, "jIM" wrote: On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I'm going to ask what may be a silly question, but have you seen the free Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM- Hide quoted text - - Show quoted text - The one you pointed me to I had figured out, there was a seperate one for an ammortization schedule, but it does not show formulas, and when I added extra principal payments, the sheet did not recalculate. The online sheet shows forumulas. When copied to an existing sheet, it does not copy forumulas. What gives? jIM- Hide quoted text - - Show quoted text - I had two sessions of excel running, it would not copy with formulas to a different session. Closed second session, opened existing workbook in current session, and copy worked with formulas. The templates "fixed" the problem, but they are way too automated (for copying and if-then analysis). I like doing a payment schedule, then highlighting in yellow the row where the payoff occurs. Then doing another analysis and highlighting that payoff row in yellow, comparing the two different schedules. The sheet allows me to do ONE analysis. Once I copy the whole structure I need to backtrack and find mistakes (the copied cells did not update when I changed the payment structure). for example, is it better to make two $625 payments in November and December of each year, or one $1250 payment in December. I know the two payments are better (based on rules of compounding), but I want to see the exact difference (in months). If payment was $2500, what is the return on paying the additional $1250 each year?- Hide quoted text - - Show quoted text - The worksheet used "named references" instead of cell IDs in equations. After about 90 minutes of work and self check, I have 2 working sheets which I am using for our 1st and 2nd mortgages. thank you for offer of assistance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mortgage ammortization schedule
You're welcome, and glad you've got it all working now.
"jIM" wrote: On Feb 16, 12:08 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Has the template been any help in tracking down your problem in your workbook? Or do you still need assistance with it? You said it wasn't updating automatically, have you checked Tools | Options | [Calculation] tab to make sure it is still set to Automatic calculating? If you recently opened a workbook with it set to manual, it could have gotten toggled. If you think there might be something I could help with but would need the workbook to dig into, feel free to email as attachment to (remove spaces) HelpFrom @ jlathamsite. com "jIM" wrote: On Feb 16, 11:00 am, "jIM" wrote: On Feb 16, 10:27 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: I'm going to ask what may be a silly question, but have you seen the free Excel loan amortization analysis and schedule templates available simply as a download from Microsoft: 'sales pitch' and explanation page:http://office.microsoft.com/en-us/ex...346401033.aspx and a template is available from this page:http://office.microsoft.com/en-us/te...415371033.aspx "jIM" wrote: I am trying to use Excel to calculate my mortgage ammortization schedule. Example: cell B2 mortage amount (-$60,000) negative number used so other numbers come out positive. C2 interest rate (7.6%) D2 term (30 years) PMT (calculated from above 3) $423.64. This matches the bank quote. A6-A365 are the dates (start April 2007 and end Mar 2037) B6-B365 are the "periods" 1-360 C6-C365 is the balance remaining on loan. This is column which does not "check" C6 is =-B2 (mortage amount) C7=C6-E6 C8=C7-E7, etc... D6-D365 is the interest paid (for that period). Forumla is ISPMT (-C $2/12,B6,D$2*12,C6) E6-E365 is principal paid (for that period). Formula is E6=B$4-D6; E7=B$4-D7 etc... B4 is the fixed payment, column D is the interest paid for that period. This series of payments "pays off" loan in 249 months (periods), according to column C. It should not be zero balance until period 360. I have a similar spreadsheet which allows for additional principal payments, but I need to get basic sheet right before concentrating on other issues. I can e-mail a working spreadsheet for anyone interested in taking a look. Thank You. jIM- Hide quoted text - - Show quoted text - The one you pointed me to I had figured out, there was a seperate one for an ammortization schedule, but it does not show formulas, and when I added extra principal payments, the sheet did not recalculate. The online sheet shows forumulas. When copied to an existing sheet, it does not copy forumulas. What gives? jIM- Hide quoted text - - Show quoted text - I had two sessions of excel running, it would not copy with formulas to a different session. Closed second session, opened existing workbook in current session, and copy worked with formulas. The templates "fixed" the problem, but they are way too automated (for copying and if-then analysis). I like doing a payment schedule, then highlighting in yellow the row where the payoff occurs. Then doing another analysis and highlighting that payoff row in yellow, comparing the two different schedules. The sheet allows me to do ONE analysis. Once I copy the whole structure I need to backtrack and find mistakes (the copied cells did not update when I changed the payment structure). for example, is it better to make two $625 payments in November and December of each year, or one $1250 payment in December. I know the two payments are better (based on rules of compounding), but I want to see the exact difference (in months). If payment was $2500, what is the return on paying the additional $1250 each year?- Hide quoted text - - Show quoted text - The worksheet used "named references" instead of cell IDs in equations. After about 90 minutes of work and self check, I have 2 working sheets which I am using for our 1st and 2nd mortgages. thank you for offer of assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mortgage Spreadsheet | Excel Discussion (Misc queries) | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) | |||
mortgage | New Users to Excel | |||
ARM mortgage amortization schedule | Excel Discussion (Misc queries) | |||
Mortgage Amortization Schedule | Excel Discussion (Misc queries) |