Help with Excel formula - calculating interest on loan

[Paq]

Can someone help please?

I lent a family member £25,000 by increasing my mortgage.

He is paying me back £250 per month.

He is now re-mortgaging himself and is asking for a "redemption statement".

I know the total amount of interest charged on my mortgage, but how can I calculate how much he has paid off?

I tried:

=sum(amount outstanding * interest rate) / 12

for monthly interest rates and

=sum(amount outstanding interest rate number of days in the month) / 365

for daily interest rates

Which is right, both or neither? Thanks :)

Anyone

hiya. I can help but it will take a mo. neither of these is right. you need to use ^ (to the power of) numbers to get these sums right.

what APR are you using? how many months has it been?

Daily interest rate is (([APR expressed as decimal e.g 0.05 for 5%]+1)^(1/365))-1
so if APR is 5%, daily interest rate is 0.0134%

Before I work out some numbers can I just check he is repaying the whole 25K and you just need to work out the interest to charge him for the period he had the money?

The amount he had outstanding was decreasing slightly during the period of the loan and of course depends on how many repayments he made.

However, that number is only right if interest is calculated and added daily. If interest is added monthly you need a different sum.

If interest is added monthly the simplest way to calculate it is by using 12 instead of 365 in the above formula. this will get you numbers that are sightly different from what the bank charged you because they will have more complex formulae which take into account the different number of days in each month and the fact that the date you make a payment will not be the same as the date they add interest. if it were my family, we would generally be happy to agree to not worry about this as the difference would be a matter of a few pounds in an unknown direction and we would agree to forgive one another this unknown amount in the interests of simplicity.

If you are happy to do this, monthly interest is (([APR expressed as decimal]+1)^(1/12))-1
so if APR is 5%, monthly interest rate is 0.4074%

each month you add this percentage to the debt, and subtract the repayment. Interest starts off at just over £100 per month but gradually reduces. After 2 years (for example) the outstanding amount is £21,435

Ooh you are all lovely

APR was 3.9% then dropping to 2.89% when I re-broked. Term was 20 years but he's overpaying so I estimate he should be paid back in 10

He's looking to pay back the total outstanding when he re-mortgages, he's in a better position (two income household now) to get his own mortgage for the full amount he needs to borrow.

With those rates and assuming you re-brokered after 2 years at the higher rate then yes he'd be on-track to be finished in 10 years assuming you can keep that 2.89% rate going for that long - remember interest rates could well rise again so if he doesn't manage to remortgage your calculations could shift again and he could end up getting there a lot more slowly.