# Thread: Annual interest amount using monthly formula

1. 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. 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. 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.

4. 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.

5. 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

6. Originally Posted by Paul Gonzalez
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.

7. 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.

8. Hi Retired Geek,

The statement
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. Originally Posted by fburg
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.

10. 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
•