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! 🙏
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!
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
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!
@@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
@@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
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?
@@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
@@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 :)
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! 🙏
How do I get the bottom of the rows of the table to read $0.00 rather than negative numbers?
Awesome video, thanks for the help. Also huge props for going through each function and why certain cells need to be locked.
Glad it was helpful!
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!
Glad it helped!
Thanks for this video it really managed to go over everything even down to the intrest saved!
Glad it was helpful!
Goodness I'm just trying to get the formulas and its TOO FAST for me to follow. Slow it down, please.
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.
Perhaps use pause function on RUclips. Might be an advance function powered by AI 😂
Thank you for this info mate. Btw are you from Auckland as well?
Thanks! Yes I do, I've been living here since 2019.
How to set Year 1 pay only interest and year 2 pay 100% interest plus Principal 10% and year 3 onwards pay full installment
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
Wow. Can you give me example for weekly payment loan 20 weeks installments and 7% interest per month
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!
@@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
@@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
@@BrentColeman it’s called MICRO FINANCE bro
Amazing. Thank you
Glad it was useful!
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?
Heya! Instalments come to about $495.29 if I've done it right. Mindful some banks calculate their loans slightly differently. Cheers!
@@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
@@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 :)
Are you single? 🙈👀
Getting married in 2 hours, so no 😂
@heretogetupdated6199 you can also see his wife in the thumbnail of his “introduction to my channel” video posted 2 months ago
@@BrentColeman lol congrats, and hilarious that you responded to a youtube comment 2 hours before your wedding. that's dedication :D
@@codyaray hahaha had some downtime 😂