Need to calculate interest paid/earned for the coming year ...

Question

Level 1

19 points

Need to calculate interest paid/earned for the coming year ...

All,

Working up the 2010 budget, and decided to give Numbers a whirl vs. continuing with M$ Excel for another year. One of the things I do is make some kind of educated estimate on how much my interest I will pay on each of my loans in the following year.

Example - auto loan:

Balance: $10,000
Interest rate: 7%
Payment: $200/month

You get the idea. Does Numbers have a formula that will calc the effective interest I would have paid by December 2010, recalculating the new principal and interest each month as the balance goes down, etc? I looked at IPMT, but some of the variables don't seem to make sense to me.

Appreciate the help in advance.

Bones

MacBookPro4,1, Mac OS X (10.6.2), 2 GB RAM :: Purchasd circa May 2007

Posted on Dec 30, 2009 11:19 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,845 points

Dec 30, 2009 12:25 PM in response to _Bones_

It would be useful to download the *iWork Formulas and Functions User Guide* (from the Help menu) then study the chapter entitled "*Financial Functions*".

Yvan KOENIG (VALLAURIS, France) mercredi 30 décembre 2009 21:25:39

Reply

Answer 2

Badunit

Level 7

31,192 points

Dec 30, 2009 7:26 PM in response to _Bones_

CUMIPMT will give the cumulative interest payments over a given period.

Starting value = 10,000
Interest rate per period = 7%/12
Periods = 5 yr * 12 mo = 60

I am pretty sure that CUMIPMT(7%/12,60,10000,1,12,0) will give you the cumulative interest paid for periods 1-12 (i.e., the first year) of the loan.

The PMT function says your payment should be $198.01/month, not $200/month. Is that incorrect?

Reply

Answer 3

_Bones_ Author

Level 1

19 points

Jan 2, 2010 10:52 AM in response to _Bones_

Ok, that works for some things. (Thanks again).

If you have a fixed-term loan (auto, home, etc.) then CUMIPMT seems to work great. But in the case of revolving credit lines, credit cards, where there is no fixed term the formula breaks down.

For example:

Credit CardX
- APR: 10%
- Balance: $5,000
- Projected payment/month: $150

Credit CardY
- APR: 12%
- Balance: $4,000
- Projected payment/month: $125

In the coming 12 months, I want to play with the numbers to see how much interest I will pay on X versus Y over the coming year ... up the payment on X and see how that changes things versus upping the payment on Y ... you get the idea.

I guess I can just create a manual formula (?)

Still appreciate any help as I am figuring out Numbers.

Bones

Reply

Answer 4

Badunit

Level 7

31,192 points

Jan 2, 2010 12:28 PM in response to _Bones_

If you get too wild, you will have to do it manually. But I believe you can use formulas for what you are trying to do. Use the NPER function to determine the number of periods required to pay off the balance based on the present value, monthly payments, and interest rate. With that known, you can use CUMIPMT to calculate the cumulative interest.

I have not tried this out so I don't know what happens with that final payment which will likely be less than the rest. I assume the functions take it into consideration but I don't know for sure.

Reply

Answer 5

Dec 30, 2009 5:45 PM in response to _Bones_

Bones wrote:
I looked at IPMT, but some of the variables don't seem to make sense to me.

B,

Which variables do you need help with?

Jerry

Reply

Answer 6

_Bones_ Author

Level 1

19 points

Dec 31, 2009 10:28 AM in response to Badunit

Thanks for the pointer Badunit. Let me work with this and report back. Looks like what I need.

Bones

Reply