Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Paso Robles, California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm new here and am looking for a little help.

    I am trying to create a loan amortization schedule with the total ANNUAL payments, interest, principal, and remaining balance displayed. However, I need the annual interest amount and related principal to be calculated based on monthly payments. Is there a way to calculate the annual interest in this manner? Any help would be appreciated.

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    If you have the monthly interest rate the annual interest is the 12th power of it, if you have the annual rate the monthly rate is the 12th root.

    If A1=0.5% monthly rate, set B1=(1+A1)^12-1 to get the annual rate, C1=(1+B1)^(1/12)-1 to get back to monthly

    Is that what you wanted? or did you want to derive the rate from the actual payments?

    Ian

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Welcome to the lounge.

    Attached is generalized loan amortization program I wrote for a client years ago that setups up a schedule to be printed for the client with all the yearly totals. It has several options as indicated by on screen boxes. You can use as is or mold to your needs.
    The client was technologically challenged ... thus all the on screen prompts and arrows.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    Paso Robles, California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the interest rate and have created an amortization schedule. Unfortunately, the schedule is lengthily because the loan is for 30 years and the payments will be paid monthly. I would like to present the schedule with only 30 lines of detail instead of 360 lines, but the annual interest each year needs to the amount that will be paid by making monthly payments, not a single annual payment. I realize I could create a schedule with 360 payments and cause the results to appear somewhere else in the workbook for presentation purposes, but I need to give the file to a client and would like to create the schedule with only 30 lines of calculations.

    The attached file shows the sample first year schedule of interest using monthly calculations at the top. The bottom section is an example of the schedule I would like to prepare.

    Thanks again for any help.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    A simple and clear way to do what you want is simply select just every 12th row for your report page as attached. Sheet 2 has the complete monthly payment table, plus two extra columns to calculate the rolling total interest and capital repayment. The VLOOKUP function in sheet 1 just looks up the last month of the year in the monthly payment table and returns the appropriate value.

    Note that I've done a couple of extra things. First, there's no need to wrap a calculation in SUM(). That function simply adds up the values of its arguments, but you only have one argument so no need to add it up! Second, I've defined range names for the input variables and the lookup tables, which makes the formulas easier to read.

    Ian
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Paul Gonzalez View Post
    I am trying to create a loan amortization schedule with the total ANNUAL payments, interest, principal, and remaining balance displayed. However, I need the annual interest amount and related principal to be calculated based on monthly payments. Is there a way to calculate the annual interest in this manner? Any help would be appreciated.

    Thanks
    Here is a modification of Ian Savell's good work to show Annual Interest and Annual Principal payments.
    .
    .

    PS: Rounding has not been implemented.
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Attached is a revised copy of the original workbook I posted that does what I suggested in the original post.
    There is now a button on the Amortization tab that will create a new worksheet listing the yearly amounts.
    This allows for loans initiated in the middle of the year showing. The values on this worksheet are all constants
    no formulas so you could copy it to a new workbook and give it to your clients w/o the supporting pages.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Retired Geek,

    I downloaded and ran your amoritization spreadsheet on Excel 2003.

    The statement
    .TintAndShade = 0
    near the end of the YearlySummary sub caused an exception. I think the error was object not supported - did you do this in Excel 2007? Anyway, I commented that line out and it worked fine. Alas, no tinting nor shading (which, I suspect, is what 0 would give me anyway - it looks like a macro recorder statement that throws everything in even if not needed).

    Thanks for the spreadsheet. I have something a little primitive like this for my home mortgage but I compute the yearly summaries, especially interest for income tax purposes, by just putting in a formula to sum the interest for the last 12 months. Your spreadsheet is definitely an improvement over my down-and-dirty attempt.

    Fred

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by fburg View Post
    did you do this in Excel 2007?
    it looks like a macro recorder statement that throws everything in even if not needed).
    Fred,

    Thanks, Yes I did use 2007 and I also used the macro recorder but though I had stripped out all of the extraneous junk but I must have missed that one. At home I usually use 2003 but I have 2007 on my laptop and I'm currently in nice Warm Southern Texas for the winter.

    Happy Holidays.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    New Lounger
    Join Date
    Dec 2010
    Location
    Paso Robles, California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A big thanks to everyone. Mission accomplished.

    As mentioned, I'm new here, so I hope to be able to contribute to the board.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •