Solved

excel formula for credit card interest pulling values from diferent spreadsheet

Posted on 2012-03-26
2
328 Views
Last Modified: 2012-08-14
I have spreadsheets setup that has every 1st and 15th of every month in one workbook. A cell in the current time period pulls the value of one cell and subtracts the payment amount from a different cell of the previous time period. The formula looks like this:  =('040112'!A34)-('040112'!A6)
I would like to add the monthly interest rate to this formula. How to I set that up?
0
Comment
Question by:knada242
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37769589
You'll need to set a range (e.g., A1) equal to your Annual Interest Rate (APR%) (unless you want to hard code it).  I'm assuming A34 holds your current balance.

As a result, I would suggest the following formula enhancement for theoretical New Balance in Column B:

[B34]=('040112'!A34)*MonthlyRate/2 - ('040112'!$A$6) 'Monthly Rate hardcoded and copy down

or

[B34] = ('040112'!A34)*$A$1/24 - ('040112'!$A$6) 'and copy down

That would take your monthly rate (based on annual APR%/12), divide by two (because you're making two payments/month, so we get a balance at the mid-period) and  multiply it by the outstanding balance, then take the bi-monthly payment at range A6 out of it.  

I'm assuming that at range A6 you have a certain payment you're using to pay down the credit card bill, and thus would need $A$6 to lock in that reference, so it is subtracted for every revised balance, as you copy down.

Cheers,

Dave
0
 
LVL 1

Author Comment

by:knada242
ID: 37773373
Thank you for your response. You got me moving in the right direction. I came up with:

=('040112'!A34)*(0.16/12)+('040112'!A34)-('040112'!$A$6)

Right now I am only calculating the interest on a monthly basis in the sheet that is right after the statement date. As time goes on I will refine it more, but this gives me a much better idea of how to build my budget.

Thank you for your help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now