Hey guys! Apologies, I spotted a mistake in the IF statement in cell C8. It should not have the extra payment in the IF TRUE section of the formula. It should instead be =if(pmt($B$2/$B$4,$B$3*$B$4,-$B$1,0,0)+F8-E8>B7,B7+E8,pmt($B$2/$B$4,$B$3*$B$4,-$B$1,0,0)+F8) . Sorry for any inconvenience! 🙏
@JMac-BCM
Ай бұрын
How do I get the bottom of the rows of the table to read $0.00 rather than negative numbers?
@ninjatacoshark
4 ай бұрын
Awesome video, thanks for the help. Also huge props for going through each function and why certain cells need to be locked.
@BrentColeman
4 ай бұрын
Glad it was helpful!
@jessmorris7202
3 ай бұрын
This video was so helpful, thank you! I was still able to follow your instructions while changing the instalments to 26 (fortnightly). I really appreciated your explanation of each step too!
@BrentColeman
3 ай бұрын
Glad it helped!
@Nanowolf100
3 ай бұрын
Thanks for this video it really managed to go over everything even down to the intrest saved!
@BrentColeman
3 ай бұрын
Glad it was helpful!
@WolfNnyl
10 ай бұрын
Thank you for this info mate. Btw are you from Auckland as well?
@BrentColeman
10 ай бұрын
Thanks! Yes I do, I've been living here since 2019.
@CynthiaTaylor-g8y
8 ай бұрын
Goodness I'm just trying to get the formulas and its TOO FAST for me to follow. Slow it down, please.
@BrentColeman
8 ай бұрын
Thank you for your feedback! I appreciate you letting me know, and I'll make sure to take it into consideration for my future videos.
@financetips
2 ай бұрын
Perhaps use pause function on KZitem. Might be an advance function powered by AI 😂
@nokoemyim
7 ай бұрын
How to set Year 1 pay only interest and year 2 pay 100% interest plus Principal 10% and year 3 onwards pay full installment
@BrentColeman
7 ай бұрын
Heya! This kind of calculation would require the use of Excel's Goal Seek feature to insert the fixed instalments into a schedule and solve for the unknown instalment value to fully amortise the loan. It's too bespoke to cover in this forum unfortunately
@natalief5509
10 күн бұрын
How do you add an option to have the variable loan rate change along the way. E.g. month 7, interest rate drops.
@BrentColeman
8 күн бұрын
This is where it gets a lot more tricky as it requires a goal seek computation, which google sheets isn’t great with. The best method would be following the same steps, but for the interest component, taking the (1) interest rate divided by 365, multiplied by (2) the number of days between each instalment eg 7/14/30/31, and again multiplying by (3) the running balance. The instalment amount will need to manually played around with until the loan schedule balance gets to $0
@sonny_bhatti
10 ай бұрын
Amazing. Thank you
@BrentColeman
10 ай бұрын
Glad it was useful!
@lucifernjay
7 ай бұрын
Wow. Can you give me example for weekly payment loan 20 weeks installments and 7% interest per month
@BrentColeman
7 ай бұрын
Heya! You can simply use the PMT formula just the same, using 20 periods. The rate will need to be adjusted from monthly to weekly - this should give an approximation. Something like this: =pmt(((0.07*12)/365)*7,20,-loan amount,0,0). Give that a try!
@lucifernjay
7 ай бұрын
@@BrentColeman it’s not work bro Loan amount 50,000 20 week instalment 6% interest per month And i need to get 3313 weekly instalment answer could you please help me bro
@BrentColeman
7 ай бұрын
@@lucifernjay I am a bit confused. Nothing you've provided mathematically works. What kind of loan is this, the interest is extremely high and oddly charged monthly while your repayments are weekly? I don't think I can help sorry
@lucifernjay
7 ай бұрын
@@BrentColeman it’s called MICRO FINANCE bro
@jalix99
7 ай бұрын
Hi, had a question about how to make this table work for a mortgage loan, 200k principle, bi-weekly payments at 5% int rate. Would you just enter those values and make the term 30 and then installments to 26?
@BrentColeman
6 ай бұрын
Heya! Instalments come to about $495.29 if I've done it right. Mindful some banks calculate their loans slightly differently. Cheers!
@1paradox
6 ай бұрын
@@BrentColemansince you didn't have a date field in your schedule, in this biweekly scenario how can I figure out how quickly I can pay off with extra payments? Like in YR2030 vs YR2040
@BrentColeman
6 ай бұрын
@@1paradox Heya! You'd need to work out the number of instalments until the occurrence you'd wish to pay by. For example, if you wanted the debt to be fully paid by 2030, you'd take 26 yearly instalments (biweekly) multiplied by about 5.75 (i.e. 5 years and 9 months). In this case, you'd estimate there's about 150 instalments out to 2030. Put 5.75 as the Term (years) and 26 as the instalments per year. Give it a try and let me know how you get on :)
@heretogetupdated6199
10 ай бұрын
Are you single? 🙈👀
@BrentColeman
10 ай бұрын
Getting married in 2 hours, so no 😂
@Kbee945
9 ай бұрын
@heretogetupdated6199 you can also see his wife in the thumbnail of his “introduction to my channel” video posted 2 months ago
@codyaray
2 ай бұрын
@@BrentColeman lol congrats, and hilarious that you responded to a youtube comment 2 hours before your wedding. that's dedication :D
Пікірлер: 33