?
Solved

excel formula for credit card interest pulling values from diferent spreadsheet

Posted on 2012-03-26
2
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 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