Solved

excel formula for credit card interest pulling values from diferent spreadsheet

Posted on 2012-03-26
2
329 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

911 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

20 Experts available now in Live!

Get 1:1 Help Now