Calculate Loan Annual Interest with Monthly Deposits

Hi,

I've managed to find a formula for calculating the periodic payments required for a loan over a fixed term, i.e. for a loan of $5,000 at 5% interest over one year =PMT(.05/12,12,-5000) = $428.04 per month.

How can I find a formula to calculate the interest owed over one year if I make monthly repayments? For example, I borrow $5,000 at 5% interest and repay $200 per month. Keeping in mind the initial $5,000 is decreasing, which decreases the monthly interest also, how can I calculate the total interest charged over one year? I hope that makes sense. :o)

Cheers,

Bill

Thanks Jeff, that looks about right. I like your logic. :o)

It's a funny coincidence, my daughter is a taking a business math class in college and I just helped her with a few of these same type of problems last week.

Here is an Excel function that you can use to find the cumulative interest paid on a loan between any start_period and end_period:

CUMIPMT(rate, nper, pv, start_period, end_period, type)

For the parameters from Post # 1, it translates into the following:

=-CUMIPMT(0.05/12,NPER(0.05/12,-200,5000,0,0),5000,1,12,0)

Jeff, that's a nice coincidence!

Root, it works just great!

Thanks to both of you for sharing your knowledge and time.