Question
Only answer Questions 2 & 3 in Excel. I need an Excel formula too. Question 1 WIth the above loan details, calculate the EMI amount
Only answer Questions 2 & 3 in Excel. I need an Excel formula too.
Question 1
WIth the above loan details, calculate the EMI amount and create the EMI schedule for the loan to fill the below table
Assumptions :
1.Payment arrives at end of period
2. Interest is applied on a monthly basis on declining balance
Balance outstanding at start of period | Payment received (EMI amount) | Principal paid | Interest paid | Balance outstanding at end of period | |
1 | 100,000 | ||||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
6 |
Question 2
Instead of 1 loan been disbursed, assumed a portfolio of loans been disbursed with % distribution of tenure (on amount), interest rate and overall disbursal as follows :
Overall portfolio disbursal | 100,000 |
Annualized Interest rate | 15% |
Monthly interest rate | 1.25% |
Assume all loans are disbursed at the above interest rate only, interest is applied monthly on declining balance method (same as in question 1)
Tenure % | Distribution |
1 | 5.00% |
2 | 5.00% |
3 | 10.00% |
4 | 2.00% |
5 | 5.00% |
6 | 25.00% |
7 | 2.00% |
8 | 2.00% |
9 | 15.00% |
10 | 2.00% |
11 | 2.00% |
12 | 25.00% |
Answer the following question (complete the empty table)
- what will be the balance outstanding of the whole portfolio at the end of every month
2. What is the payment received every month
3. What is the principal paid and interest paid every month
Balance outstanding at start of period | Payment received (EMI amount) | Principal paid | Interest paid | Balance outstanding at end of period | |
1 | 100,000 | ||||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
6 | |||||
7 | |||||
8 | |||||
9 | |||||
10 | |||||
11 | |||||
12 |
Question 3
Instead of 1 loan been disbursed, assumed a portfolio of loans been disbursed with % distribution of tenure (on amount), interest rate and overall disbursal as follows :
Overall portfolio disbursal | 100,000 |
Annualized Interest rate | 15% |
Monthly interest rate | 1.25% |
Assume all loans are disbursed at the above interest rate only, interest is applied monthly on declining balance method (same as in question 1)
Tenure % | Distribution |
1 | 5.00% |
2 | 5.00% |
3 | 10.00% |
4 | 2.00% |
5 | 5.00% |
6 | 25.00% |
7 | 2.00% |
8 | 2.00% |
9 | 15.00% |
10 | 2.00% |
11 | 2.00% |
12 | 25.00% |
The above details are exactly same as question 2 but now assume that some loans also get foreclosed and the coincidental foreclosure curve is as follows
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
Foreclosure Curve - POS | 4.44% | 2.58% | 2.47% | 2.35% | 2.32% | 2.36% | 2.17% | 2.22% | 2.26% | 2.17% | 2.33% | 2.72% |
How to read the foreclosure curve
Suppose at the start of period 2 balance outstanding of the portfolio is 100, then apart from the normal EMI paydown, additional 2.58 will be paid as foreclosure
Similarly if at the start of period 5, balance outstanding of the portfolio is 50, then apart from the normal EMI paydown, additional 1.16 (i.e 2.32% * 50) will be paid as foreclosure
Answer the following question (complete the empty table)
1.what will be the balance outstanding of the whole portfolio at the end of every month
2.What is the payment received every month
3.What is the principal paid and interest paid every month
Balance outstanding at start of period | Payment received (EMI amount) | Principal paid | Interest paid | Balance outstanding at end of period | |
1 | 100,000 | ||||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
6 | |||||
7 | |||||
8 | |||||
9 | |||||
10 | |||||
11 | |||||
12 |
\begin{tabular}{l|r} \hline Loan value & 100,000 \\ \hline Annualised Interest rate & 15% \\ \hline Monthly interest rate & 1.25% \\ \hline Tenure & 6 \end{tabular}
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started