Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Kentucky, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macros/Amortization (XP)

    I have found a loan amortization table (template) from a previous post. I would like to get rid of all the macros because I'm not that familiar with them. I find it easier to manipulate the spreadsheet with just the basic formatted cells. Am I making any since? Can any one help me? The tables calculate different from what I need. I know how much the loan is, interest rate, monthly payment amount, and it needs to be made in 16 payments over the next 16 months. The templates I have found want to calculate based upon number of years. If I can't change the macros, can any one help with a new amortization schedule? <img src=/S/eargear.gif border=0 alt=eargear width=20 height=20>

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    <pre></pre>

    Can you give us a link to the post you're referring to - or alternatively tell us which number post it is? This will help us respond to your question much more clearly.

    At a very rough guess as to what you're looking to work out, you may find looking at the CUMIPMT function in Excel Help useful.

    If you <big>really</big> want to get rid of all the macros:<UL><LI>Press Alt+F11. This will get you into the Visual Basic Editor (VBE).<LI>In the top left hand corner of the VBE, the will be a window called the Project Explorer. In it will be a list of VBAProjects, including one which will say VBAProject([your amortization file].xls).<LI>Select that file; clicking on the + sign beside it if necessary.<LI>There should be a folder called Modules. Click on that folder. <LI>There may be one or more modules. These are what contain the macros. <LI>Select each module and then choose File|Remove (the fourth item in the File drop down list).<LI> You will be asked whether you want to export the module before you delete it. Once you have dealt with that, then all the macros in that module will be permanently removed.[/list]Not having seen the template, naturally, I can't comment as to whether it will run without the macros. HTH
    Gre

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macros/Amortization (XP)

    There are some threads that discuss loan/mortgage/future value functions without templates or macros in Excel <!post=Post 100364,100364>Post 100364<!/post> and <!post=Post 101771,101771>Post 101771<!/post>. Without knowing exactly what you want, can you start in Excel Help by searching for "Financial Functions", select "About financial functions", and review for what suits your need. If you want, post exactly what you are trying to do here in Excel and someone will be able to help.
    -John ... I float in liquid gardens
    UTC -7DS

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Kentucky, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    I did the Alt+F11 and deleted the macros and that still did not give me what I'm wanting. I don't need all the headings, fill in options, etc....I prefer a simple basic spreadsheet....do I make any since? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks....

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    As you can see, the template itself was very purpose built by (or on behalf of) Microsoft itself.

    As you may have already found out, deleting the macros simply wipes out the entire amortization schedule. I don't think that's what you want - as your second post (which has just arrived) clearly indicates.

    You seem to have most of the information you need already. Can you just be a bit more specific, step by step, with what you are actually trying to do? If we can be told that, then it will be much simpler to help you move in the right direction.
    Gre

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Kentucky, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    Looking at the template, I want the "loan data" and the "loan amortization table" to be on 1 spreadsheet. With the loan data at the top and the table below. Do you know how I can copy the formulas with out all the headings? And still keep the formulas in tact with the table below it? All I need from the "loan data" is the basic, payment and summary info.
    It appears the table calculates based on the number of years and that is great in most cases. However, I would like to have the option to calculate by number of months and/or number of payments. I know the loan amount, interest rate and the monthly payment. Can the schedule be done based upon the number of payments? Number of months? And the interest be compounded monthly?

    I am trying to get the breakdown of each payment. I need to know how much of each payment received is interest and how much is principal.

    Sorry, I seem to be having a hard time explaining exactly what I need. I hope this is a little more detailed so that maybe you can send me in the right direction.

    Thanks much....

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macros/Amortization (XP)

    Then you definitely need to look at my attachment to Excel <!post=Post 100455,100455>Post 100455<!/post>, which is specific to your question, although it gives only a partial model you can rework to suit your needs.
    -John ... I float in liquid gardens
    UTC -7DS

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    <pre></pre>

    Some of what you are asking for requires quite a bit of work - like the option to convert the table into monthly payments.

    Other bits are relatively simple: such as moving the Loan Data box to the top of the Amortization table. To do this <UL><LI>Highlight the first column in that sheet, as far as the box goes, <LI>Press Shift and the Tab bar simultaneously, <LI>Go to the Amortization Sheet and in the top left hand corner press Shift and the Tab Bar simultaneously again. <LI>Then press Control and the Plus sign (on your number pad) simultaneously.[/list]Without a fair amount of work, however, deleting the Loan Data sheet would make the workbook "collapse".

    Even by deleting the graph, and compressing the code module, the zip file doesn't seem to quite squeeze under the 100Kb posting limit.

    Given this limit, a workbook based on a monthly payments would be the subject of a separate post. Despite the amount of presentation, there are a fair amount of solid formulas there as well. Maybe a Lounger will help you out.

    Do have a look at the post that JohnBF is mentioning. Something more "all singing all dancing" will take more time, but it is now quite clear what you are looking for.
    Gre

  9. #9
    New Lounger
    Join Date
    Mar 2002
    Location
    Kentucky, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    <P ID="edit" class=small>Edited by WebGenii on 25-Apr-02 23:03.</P>There are three different post and all were made in Sept. 2001. The one with the zip template file is <!post=78142,78142>78142<!/post>. The other two post that correspond are <!post=78236,78236>78236<!/post> & <!post=78821,78821>78821<!/post>.

    I have never went into the VBE and I am not that familiar with "all" the different functions and what each one does but I'll give it a try. Guess you could say I'm still in the learning process. In the meantime, should you have any other suggestions, I am all "ears"!

    I appreciate any help.

    Thanks...

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macros/Amortization (XP)

    Is the attached zip file closer to what you want? It permits you to select either the period in months, or the payment amount desired. Payment amount is then microadjusted based on the nearest rounded number of months. Works for up to 360 months. (Sheet protection is without a password. Will needs some cleanup work for printing.)
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7DS

  11. #11
    New Lounger
    Join Date
    Jul 2001
    Location
    Martinsburg, WV
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    I'm just curious what this worksheet is supposed to do--every cell below row 10 has #NAME? in it. Is this sheet supposed to work in Excel 2000? I've been looking for something like this for awhile, but it doesn't seem to be working correctly. I tried entering some other numbers in B1-B5, but I keep getting #NAME?

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    Select the Addins command from the Tools menu. Put a check next to "Analysis Toolpak" in the list.
    Legare Coleman

  13. #13
    New Lounger
    Join Date
    Jul 2001
    Location
    Martinsburg, WV
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    Hot dog!

    Thanks a lot, Legare--that did it! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macros/Amortization (XP)

    Sorry, I've been working with the Analysis Toolpack installed for so long that I forget what's in standard Excel installation and what's in the Toolpack. In the schedule, =NPER() and =PMT() formulas are built into standard Excel installation, but =CUMPRINC() in column D, which drives all of the detailed schedule, requires the Analysis Toolpack.

    Thanks, Legare, for helping out.
    -John ... I float in liquid gardens
    UTC -7DS

  15. #15
    New Lounger
    Join Date
    Mar 2002
    Location
    Kentucky, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros/Amortization (XP)

    John, thanks so much, however, (and I hate to ask...but....) I selected the Addins command and the cells below row 10 are still showing #Name?. Do I need to do this running office xp?
    Where is the proper place to enter the monthly payment amount and have the schedule breakdown the interest and principal each month? <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Page 1 of 2 12 LastLast

Posting Permissions

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