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'm trying to calculate my Amortization table.

Details as follows

Principle $5000

59 installments $121.47

final installment $122.95

60 months

interest 9.39% pa

currently payments are $150/month (1 payment made and balance is $5738.99)

I have attached file.

I appologise I should know this but my brain isn't working :(

                       
Answer
Discuss

Answers

0
Selected Answer

Albert

In the attached file, columns A:D show "Regular payments using PMT and PPMT functions". This is a modified version of the Excel-easy file (from Willie's suggested URL). With your basic loan details (and the addition of rounding to tehir formulae), PMT is used in B to determine the expected payment and PPMT the reduction in the laon balance (in C) per month. The balance in D then reduces to very nearly 0 after the 5 years. Orange cells indicate differing formulae.

Columns F:H however allow for your extra payment (to be set once only in yellow cell F9). In column H, the balance is calculated as the (remaining principal plus monthly interest) less the total monthly payment. In H10 (and cells below), that is:

=ROUND(H9*(1+$G$3/12)-G10,2)
where H9 is the previous balance and G3 is the annual interest rate, G10 the monthly payment.

With F9=0 (like the original loan repayments) and no adjustment to the final payment, the balance reduces to a few cents only after the 5 years (so very close to the PMT and PPMT calculations).

Set F9=28.53 (so an extra), the total monthly becomes $150 as you say and the balance reduces to 0 faster, the final payment being at 47 months.

Note I've used If statements  in columns F and G to work out what happens as the balance approaches 0 (plus conditional fomatting to indicate the area when no payments are needed).

Hope this is good enough for your purposes.

Discuss

Discussion

Thanks John

That is very good .

I just need to change the amounts to possitive and insert a one of extra payments column and interest column. They only direct debit once per month and you have to manually do any other payments (other than increased monthly payment).

Then I'll do some automatic calculations on how much I save and how much time by having other payments.

Thanks again John.
wildecoyote1966 (rep: 28) Nov 7, '21 at 7:14 pm
Glad it helped, Albert.

I know it's not an Excel issue but on the extra payments, won't your bank allow you to set up a Standing Order (e.g.$28.53 paid on the 15th of each month)?
John_Ru (rep: 3992) Nov 8, '21 at 8:25 am
They will increase the repayment to 150 or whatever once a month as they only have a set day per month they do repayments. I did manage to setup a payment direct from my bank so that's good. I got them to do an Amortization schedule however even theirs doesn't match reality. :( same issue with other laon we worked on.
wildecoyote1966 (rep: 28) Nov 8, '21 at 9:51 pm
Add to Discussion
0

Hello wilde,

I did a quick search of the site but didn't find a tutorial on building an amortization table. I did a quick Google search and found this in under a minute:

https://www.excel-easy.com/examples/loan-amortization-schedule.html

I also discovered that you made a very similar post Sept. 7 and Sept. 20.

Information at the above url should give you the help you need.

Discuss

Discussion

Hi Willie

The other Amortization questions were for a very different loan.
but thanks for the link
wildecoyote1966 (rep: 28) Nov 6, '21 at 7:57 pm
Add to Discussion


Answer the Question

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