Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# excel formula for credit card interest pulling values from diferent spreadsheet

Posted on 2012-03-26
Medium Priority
353 Views
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
[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

LVL 42

Accepted Solution

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

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.

0

## Featured Post

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.