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

amortization

0

I am trying to set up the amortization schedule for a home loan.

Interest is 2.69% and is calculated daily, charged monthly

loan is 19 years 4 months

payments every fortnight (about 26/year)

I have included the file.

sheet1 actual payments and interest upto end of december.

Sheet 2 calulation test page

Sheet 3 amortization schedule.

I have looked at a few sheets online and don't quite fit.

my schedule (sheet 3) isn't quite meeting the same figures as sheet 1 so I'm wondering where I'm going wrong

EDIT

I have uploaded an aditional file.

I got a lot sort but having trouble with consistancies.

formulas not being consistant, rounding not the same....

can someone please have a look at it for me

excelfinance
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Mon Sep 20, 2021 at 10:16 am
Answer
Discuss

Discussion

Can I automatically have a line entered say EOM?
Edit
oh and ignore sheet 2 if you like. that was getting the final to work.
wildecoyote1966 (rep: 30) Sep 20, '21 at 8:56 pm
Wilde. I just glanced at sheet2 then ignored it. Did my Answer suggestions help narrow the gap between sheets?

The EOM point feels like an additional question (for which I'd use VBA) 
John_Ru (rep: 6142) Sep 21, '21 at 1:49 am
Yes it did narrow the gap.
I guess you're probably right about the EOM being a different question but I think if I can achieve that I may be able to get this one where I want. 

Did you see the update for my interest question 
wildecoyote1966 (rep: 30) Sep 21, '21 at 2:24 am
Wilde- I didn't see the update in the question (nor in the file- where it shouldn't be!). Please add it to the original question or remind me here. I'll then give my VBA suggestion (well, later this morning here).
John_Ru (rep: 6142) Sep 21, '21 at 2:55 am
Add to Discussion

Answers

0
Selected Answer

Wilde

I think there are two reasons the sheets don't match: formula error and a couple of extra entries.  In the attached version of your file, I've made some changes.

In column G  of sheet "Final" (e.g. G15) the formula is:

=IF(G14="","",IF(E15<>"",G14+E15,IF(F15<>"",G14-F15,"")))

but that fails to deduct the "date difference" interest added. I've changed the test (to if either E or F has content) and aded the interst from column M, so with chnages in bold: 

=IF(G14="","",IF(COUNT(E15:F15)>0,G14+M15-F15,""))

In the sheet "Amort" the yellow cells show where I removed two additional payments in E18:E19 (not present in Final).

Finally, your column I of Amort had many decimal places (as do ther sums (in the background) but I suspect the values are rounded to 2 decimal places (liike most currencies) so changed the formula to:

=IF(A18="","",IF(18="","",ROUND((C18*($C$6/365)*(B18-B17)),2)))

The equivalent cell for interest in Final (e.g. M15) now rounds to 2 dp with this formula:

=IF(A18="","",IF(18="","",ROUND((C18*($C$6/365)*(B18-B17)),2)))
Now arguably this brings in rounding errors bt I leave you to think that through.

The result is that your closing balances match better but still drift apart a bit.

Hope this helps.

posted by: John_Ru (rep: 6142) on Mon Sep 20, 2021 at 2:58 pm
Discuss

Discussion

Hi John
I uploaded a new workbook. I have a red highlighted section with some comments.
Albert
wildecoyote1966 (rep: 30) Sep 22, '21 at 11:25 am
Albert

I'll answer you here but you shouldn't extend a question with different topics and definitely not put them in the spreadsheet (see Rules). To your three questions on sheet Amort of the file Copy... my answers are:

1) Cell F29 is different to the matching figure on Final E26 . 

A. No, both have the same value of$518.41  

2) Cell B32 behaves different to the above cells (formulas).  

A. Think you've removed the data validation on column B- see cells in Homeloan.xlsx and re-apply  

3) The title coloured blue have these comments appear and I can't work out how to remove them . 

A. Regarding the blue cells in sheet Amort and the "floating" comments which appear when you hover over them (but without the attachment line with Comments or Notes), these are actually Input Message under the cell's Data Validation. To remove them, click on a cell, go ribbon Data/(Data Tools section)/Data Validation a d either click ClearAll or untick "Show input message..." in the Input Message tab (where you'll see the displayed wording).. Alternatively select all the blue cells the follow the button path I mentioned and respond the requestor which comes up to clear all data validation.

At this point, I think I've answered your original question (and extra ones) so you ought to select that answer and ask further questions afresh (having checked the Rules please)

Hope this helps.
John_Ru (rep: 6142) Sep 22, '21 at 2:59 pm
Add to Discussion


Answer the Question

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