Results 1 to 11 of 11

Thread: Excel help

  1. #1
    Member
    Join Date
    Feb 2013
    Location
    UK
    Posts
    145
    Thanks
    8
    Thanked
    5 times in 3 posts
    • Preacher's system
      • Motherboard:
      • ASRock z77 Pro4m
      • CPU:
      • i7 3770k
      • Memory:
      • 8GB GSkill 1600Mhz
      • Storage:
      • Samsung 500GB 840, 6TB of Seagate in JBOD
      • Graphics card(s):
      • MSI 7950 Twin Frozr
      • PSU:
      • Seasonic X660
      • Case:
      • Antec P180
      • Operating System:
      • Win 8 Pro
      • Monitor(s):
      • Two old ones from work
      • Internet:
      • Virgin 60mb

    Excel help

    I'm trying to make a personal finances spreadsheet to help keep a track of our income/outgoings.

    Does anybody know a formula that will calculate interest on a credit card? I'm trying to work out how many months it would take to pay off if I pay x amount.

  2. #2
    Senior Member Smudger's Avatar
    Join Date
    Oct 2005
    Location
    St Albans
    Posts
    3,866
    Thanks
    674
    Thanked
    619 times in 451 posts
    • Smudger's system
      • Motherboard:
      • Gbyte GA-970A-UD3P
      • CPU:
      • AMD FX8320 Black Edition
      • Memory:
      • 16GB 2x8G CML16GX3M2A1600C10
      • Storage:
      • 1x240Gb Corsair M500, 2TB TOSHIBA DT01ACA200
      • Graphics card(s):
      • XFX Radeon HD4890 1GB
      • PSU:
      • Corsair HX520
      • Case:
      • Akasa Zen
      • Operating System:
      • Windows 10 Home
      • Monitor(s):
      • Dell 24"
      • Internet:
      • Virgin 200Mbit

    Re: Excel help

    Could just do "Balance x 0.whatever the interest rate is"? Remember to take the APR and divide by 12 to get the monthly APR...

  3. Received thanks from:

    Preacher (12-03-2013)

  4. #3
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,168
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel help

    Code:
    =A1*(1+B1)^C1
    A1 is the amount on the card.
    B1 is a percentage
    C1 is the duration.

    So now comes the fun bit. How is interest applied? Is it daily, monthly or yearly?

    It's probably monthly, so if your card charges 7.9% devide it by 12 like so, then C1 becomes months.
    Code:
    =A1*(1+(B1/12))^C1
    throw new ArgumentException (String, String, Exception)

  5. Received thanks from:

    Preacher (12-03-2013)

  6. #4
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H

    Re: Excel help


  7. Received thanks from:

    Preacher (12-03-2013)

  8. #5
    Member
    Join Date
    Feb 2013
    Location
    UK
    Posts
    145
    Thanks
    8
    Thanked
    5 times in 3 posts
    • Preacher's system
      • Motherboard:
      • ASRock z77 Pro4m
      • CPU:
      • i7 3770k
      • Memory:
      • 8GB GSkill 1600Mhz
      • Storage:
      • Samsung 500GB 840, 6TB of Seagate in JBOD
      • Graphics card(s):
      • MSI 7950 Twin Frozr
      • PSU:
      • Seasonic X660
      • Case:
      • Antec P180
      • Operating System:
      • Win 8 Pro
      • Monitor(s):
      • Two old ones from work
      • Internet:
      • Virgin 60mb
    Thanks for the replies. The best I can up with was

    =(A1*(1+A2))/A3

    Where A1 is balance, A2 the APR and A3 the monthly repayments but its not right.


    @smudger if I did that it wouldn't show me the months until cleared

    @theanimus are you putting the balance times interest to the power of months?

    @malfunction I did look at google but its not the same as loan repayments as I'm trying to find the months until the credit card is paid off. A loan calculator has the duration as a constant and uses that to find the repayment amount.

    That function does look like it should work though will give it a try thanks

  9. #6
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,168
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel help

    Excel processes indicies before multiplies, so its correct.

    You need to raise the interest rate by the power of the duration.

    My post is correct no?
    throw new ArgumentException (String, String, Exception)

  10. #7
    Member
    Join Date
    Feb 2013
    Location
    UK
    Posts
    145
    Thanks
    8
    Thanked
    5 times in 3 posts
    • Preacher's system
      • Motherboard:
      • ASRock z77 Pro4m
      • CPU:
      • i7 3770k
      • Memory:
      • 8GB GSkill 1600Mhz
      • Storage:
      • Samsung 500GB 840, 6TB of Seagate in JBOD
      • Graphics card(s):
      • MSI 7950 Twin Frozr
      • PSU:
      • Seasonic X660
      • Case:
      • Antec P180
      • Operating System:
      • Win 8 Pro
      • Monitor(s):
      • Two old ones from work
      • Internet:
      • Virgin 60mb
    I wasn't saying it was wrong, just didn't quite understand it..

  11. #8
    Senior Member
    Join Date
    May 2009
    Location
    Norfolk
    Posts
    474
    Thanks
    3
    Thanked
    26 times in 26 posts
    • pipTheGeek's system
      • Motherboard:
      • Asus P6T Deluxe
      • CPU:
      • Core i7 920 @ 3.6GHz
      • Memory:
      • 3 * 2Gb Corsair XMS @ DDR3 1800
      • Storage:
      • 300GB 15K SAS + 500Gb
      • Graphics card(s):
      • GTX570
      • PSU:
      • corsair 760i
      • Case:
      • Corsair 550d
      • Operating System:
      • Windows 7
      • Monitor(s):
      • Dell Alienware 23"
      • Internet:
      • VM 50Mb

    Re: Excel help

    If interest is compound and being calulated monthly then you can't just divide the annual rate by 12. You actually need to solve
    1+<yearly interest>=12^<monthly interest>
    for <monthly interest>. If I remember my GCSE maths correctly that becomes
    10^(log(<1+<yearly interest>)/12)
    Change the 12 to however many periods you need to divide the interest between.

    I was going to provide the actual excel fomulea I used to do exactly this. But. I can't find the workbook. Sorry. I never managed to solve the general equation for how long it would take to pay off a loan, I ended up using the above to work out the daily interest rate then had an excel calculation that applied that interest to the running total, minus any payments made (listed in another column) and just dragged that down as many rows as I needed until the total was 0. (With the date in another column also just dragged down as far as needed).

    From looking at the NPER function that malfunction linked to it would appear that it will do the how many payments part, you just need to feed it the correct interest rate which will probably mean working out the monthly compound rate from the annual rate.

  12. Received thanks from:

    Preacher (12-03-2013)

  13. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    2,401
    Thanks
    87
    Thanked
    151 times in 145 posts
    • Willzzz's system
      • Motherboard:
      • Gigabyte
      • CPU:
      • 4670K
      • PSU:
      • FD Newton R3 600W
      • Case:
      • Corsair 350D

    Re: Excel help

    x = monthly interest

    1+ apr = (1+x)^12

    so if the apr is 10%

    1.1 = (1+x)^12
    1.1^(1/12) = 1+x

    monthly interest = 0.797%
    Last edited by Willzzz; 12-03-2013 at 09:32 PM. Reason: silly mistake!

  14. Received thanks from:

    Preacher (12-03-2013)

  15. #10
    Member
    Join Date
    Feb 2013
    Location
    UK
    Posts
    145
    Thanks
    8
    Thanked
    5 times in 3 posts
    • Preacher's system
      • Motherboard:
      • ASRock z77 Pro4m
      • CPU:
      • i7 3770k
      • Memory:
      • 8GB GSkill 1600Mhz
      • Storage:
      • Samsung 500GB 840, 6TB of Seagate in JBOD
      • Graphics card(s):
      • MSI 7950 Twin Frozr
      • PSU:
      • Seasonic X660
      • Case:
      • Antec P180
      • Operating System:
      • Win 8 Pro
      • Monitor(s):
      • Two old ones from work
      • Internet:
      • Virgin 60mb
    My god this is complicated!! Watching barca v Milan so going to have a go later. Thanks for all the replies

  16. #11
    Senior Member
    Join Date
    May 2009
    Location
    Norfolk
    Posts
    474
    Thanks
    3
    Thanked
    26 times in 26 posts
    • pipTheGeek's system
      • Motherboard:
      • Asus P6T Deluxe
      • CPU:
      • Core i7 920 @ 3.6GHz
      • Memory:
      • 3 * 2Gb Corsair XMS @ DDR3 1800
      • Storage:
      • 300GB 15K SAS + 500Gb
      • Graphics card(s):
      • GTX570
      • PSU:
      • corsair 760i
      • Case:
      • Corsair 550d
      • Operating System:
      • Windows 7
      • Monitor(s):
      • Dell Alienware 23"
      • Internet:
      • VM 50Mb

    Re: Excel help

    Quote Originally Posted by Willzzz View Post
    x = monthly interest

    1+ apr = (1+x)^12

    so if the apr is 10%

    1.1 = (1+x)^12
    1.1^(1/12) = 1+x

    monthly interest = 0.797%
    That works and looks easier than my solution, although I don't follow what you did there. Specifically, why is 1.1 = (1+x)^12 the same as 1.1^(1/12) = 1+x? I wish I could remember more of my maths. You wouldn't believe that I did A level further maths would you?

    As a side note, I got my initial sum upside down, it should have been 1+<yearly interest>=<monthly interest>^12
    Although I think I corrected it in the next line without noticing.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •