Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Interest

0

Can I get excel to automatically calculate interest which is

Calculated daily

charged monthly?

EDIT

After help from John_Ru and WillieD24 this has been achieved. see the attached file

"copy of homeloan Test - new CF rules-final"

Once you enter the date then the details a highlighted square appears (conditional formating - please see that area for details) and for Repayment you enter figure and for Interest it calculates the value (see following)

Please note column B is the date.

To achieved this first I calculated the month column H (Green), =IF(B14="","",MONTH(B14))

Then I calculated the interest for the number of days between activity Column I (Green)

Interest calculated for days between activity, =IF(B16="","",IF(C16="","",(G15*($C$3/365)*(B16-B15))))

Finally the Interest is calculated by Column E =IF($B18="","",IF($C18="Interest",SUMIF($H$14:$H$163,"="& H18,$I$14:$I$163),""))

so basically it calculates as:

Calculate number of days between activity(Bx-By)

Then all indiviual interest amounts for month are added together

I will add an Amortization Schedule for comparison etc at a later date

EDIT

I have now uploaded the completed file with Amortization Schedule included.

Answer
Discuss

Discussion

Hi and welcome to the Forum

Do you mean that the interest is a fixed daily rate or it changes on a daily or monthly basis (and you're prepared to enter that rate, day-by-day)? It would help if you would attach an Excel file showing how the values change typically (using test data only) - you can do that by editting your original question and using the Attach Files... button. Kindly also expand your question text to outline how interest/ repayments vary.
John_Ru (rep: 6102) Sep 8, '21 at 6:40 am
Interest rate 2.69% fixed rate for 3 years
Opening value $237,500
Repayments 650 per fortnight 
I'll upload file when I get back to my laptop 
wildecoyote1966 (rep: 28) Sep 8, '21 at 6:57 am
Repayments 650 per fortnight? There's something wrong here- monthly repyaments wouldn't even cover the repayment of capital in 3 years (you'd need about $3k/fortnight to repay that amount/rate). 
John_Ru (rep: 6102) Sep 8, '21 at 12:55 pm
Hi John

Fixed rate is for 3 years actual loan is 19 years and 4 months
Loan changes to variable rate after 3 years.
wildecoyote1966 (rep: 28) Sep 8, '21 at 4:56 pm
Add to Discussion

Answers

0
Selected Answer

Wilde

I guess you're trying to see how your mortgage value is amortized. If you undertand how the interest is applied c.f. your repayments then you could create a large spreadsheet to calculate how payments affect the value of your mortgage.

Also Excel has built-in functions related to loans/interest/payments (PMT, IPMT,  PPMT, FV, FVSCHEDULE).

I suggest however that you first try something like this website Microsoft Excel Mortgage Calculator With Amortization Schedule where you need only add a few figures to get a result.

Discuss

Discussion

Hi John.
I asked the help guy at the loan company how interest was applied. but he didn't know and then I asked for an Amortization schedule and he said what's that then I told him.
I got the finance company to make one but it stopped matching after the second payment. I'd email it to you but don't want all of it online. Same as the other schedule we did. matched for a bit then stopped lining up . I thought this one should be easy but grrrrr
wildecoyote1966 (rep: 28) Nov 9, '21 at 7:05 pm
We're straying if Excel here but I think your best bet to is ask lenders when your loan will be paid if you increase payments to X. I can't see them accepting Excel calculations instead of their corporate models, especially if they are they are tightly regulated (as they are here in the UK). Treat your calculations as indicative, for testing what might be. 
John_Ru (rep: 6102) Nov 10, '21 at 1:32 am
good idea.

Thanks John
wildecoyote1966 (rep: 28) Nov 10, '21 at 7:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login