Page 8 of 9 FirstFirst ... 6789 LastLast
Results 71 to 80 of 82

Thread: Excel Sorcerers!

  1. #71
    Senior Member
    Join Date
    Jan 2014
    Location
    Sydney, Australia
    Posts
    2,293
    Good point!! Using formatting rather than modification.

    Using modifying if you need to operate on the data, eg round numbers, then sum. If you just want to display rounded, use formatting. But rounding before summing is different to summing then rounding.

  2. #72
    Junior Potato
    Join Date
    Jan 2014
    Posts
    9,600
    Yeah thanks for the suggestions. Unfortunately I can’t do much rounding because I’m not sure what level of precision I need just yet. It depends on the task I guess.

    Plus I’ll need to rework some parts of it because I need the inverse equations too, and the molarity section is completely backwards and not user-friendly!

  3. #73
    Senior Member
    Join Date
    Jan 2014
    Location
    Cambridge, UK
    Posts
    843
    Thought I'd quickly knock up what I was thinking:

    https://docs.google.com/spreadsheets...N1o/edit#gid=0



    The formulas for the green cells are just either B / C or B x C, as appropriate, and then formatted as number or percentage as needed.

  4. #74
    Junior Potato
    Join Date
    Jan 2014
    Posts
    9,600
    Hey Excel people.

    Today's question:

    I want to calculate interest on a loan that I'm paying off. Can I do something elegantly and easily that:

    1. Calculates interest daily
    2. Allows me to input my repayments weekly or monthly
    3. Spits out a total interest payment
    4. Allows me to compare between different repayment scenarios?

    I have used the instructions highlighted HERE and with some adjustment can change between weekly and monthly payments, but I don't think it calculates interest daily, and won't give me an interest amount result.

    I have also built a really cumbersome table where on each row I take the previous month's balance, then add 30 days of interest, then subtract a monthly payment... repeat. I then manually find where it reaches zero and then add up the interest column.

    Any ideas would be splendid, thank you.

  5. #75
    Senior Member
    Join Date
    Jan 2014
    Location
    Sydney, Australia
    Posts
    2,293
    Calculating interest daily is no different to calculating it weekly/monthly other than its effectively a higher rate ie compounding more frequently is same equation just different numbers.

    As for weekly vs monthly - you do need to check if the repayments are equivalent.
    Bank typically highlight that fortnightly repayments pay off the loan quicker than monthly. But this is mostly because you are paying more! They assume 2 week payment is half the monthly, but then there is 26 payments per year vs 12. Not surprising 26*.5=13 is bigger than 12. So you mostly pay it off quicker due to paying more. The "compounding" difference is tiny.

    None of this is tricky Excel, its just getting the correct formulae for compound interest with repayments.
    If you are just having a set repayment then its a single equation.
    The only reason to do the "each row is a payment" is if you wanted to vary the repayments up/down as you go.

  6. #76
    Junior Potato
    Join Date
    Jan 2014
    Posts
    9,600
    All understood.

    However (at the risk of getting into the weeds of loan repayments a little bit) when the bank calculates interest daily, it's calculated based the balance of the loan each day but then charged once a month. So it might add $2/day but you don't see a charge until the end of the cycle when they plop $60 on it.

    On top of this you might make a repayment into the loan account once a month, so it's continually calculating interest on the same balance over that 30 days, whereas if you pay once a week the interest is calculated based on the reduced balance thanks you you reducing it by a weekly payment rather than monthly. Assuming you pay the same amount over time between weekly or monthly payments, you actually do pay it off slightly faster due to the daily interest calculation, when ignoring the occasional 5-week month.

    I suppose I probably could use the example formula in the link I provided and just multiply my monthly or weekly payments, and subtract one from another. In fact, testing it out it seems like a good way to go, as my cumbersome table has an error in that I calc'd everything on a 30-day month, and not all months have 30 days.

  7. #77
    Senior Member
    Join Date
    Jan 2014
    Location
    Cambridge, UK
    Posts
    843
    It sounds like you've got it worked out, use NPER to calculate the number of payments as a function of the loan total, regular payment amount and number of payments per year (12 or 52 respectively). Then you know how much you're paying and can work the rest out from there.


    On the daily interest calculation, it sounds like the important things are that the amount on which interest is charged will change immediately after each payment, and monthly based on interest.

    The one thing the built-in formulas probably won't support is interest charges applied monthly (if that's what you're saying), but payments applied weekly. I suspect in this case the formula will just use a weekly cycle for everything, so the calculation will end up being fractionally more expensive than reality as it'll model you paying interest on the interest, so to speak. I also suspect this will make only a negligible difference.

  8. #78
    Junior Potato
    Join Date
    Jan 2014
    Posts
    9,600
    Yeah difference is only a few bucks. But at the end of the day I have worked out that a $500 investment to fully pay off a credit card debt now will allow me to accelerate loan repayments, get it paid off in 14 months, and save myself about $340.

    I just had a few scenarios in my head and wanted to work out which one was the best. Second-best will save me $310. It was close, so that's why I wanted to do some numbers

  9. #79
    Senior Member
    Join Date
    Jan 2014
    Location
    Sydney, Australia
    Posts
    2,293
    While they might charge daily, their advertised rate should be the equivalent.

    Now another way to look at this "repay daily, weekly, monthly, yearly"... paying more frequently is no better than making the other payment at the start and saving interest on the amount of that repayment.
    eg if I repay say $200 a week, instead of $800 a month... at best its like you got that $800 in before the loan started. You are saving interest on $800. Which is negligible compared to the overall loan.

    Its not really worth wasting time on frequency of repayment other than having it match your frequency of income!! And if monthly even have it due soon after you get paid. The banks dont like paperwork, but they will move the repayment date if you ask.

    (sorry gone off Excel talk)

  10. #80
    Ask me about my bottom br FaultyMario's Avatar
    Join Date
    Jan 2014
    Location
    ox.mx
    Posts
    8,233
    Quote Originally Posted by Dicknose View Post
    paying more frequently is no better than
    The gift that keeps on giving.
    acket.

Posting Permissions

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