Solved

excel formula for credit card interest pulling values from diferent spreadsheet

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

739 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