Create a workbook named:
Financial_Functions_Practice.xlsx
Section 1 — Loan Analysis
Use the following dataset:
|
Customer |
Loan Amount |
Annual Rate |
Years |
|
Amit |
500,000 |
10% |
5 |
|
Neha |
800,000 |
9% |
5 |
|
Rahul |
1,000,000 |
8.5% |
10 |
|
Priya |
600,000 |
9.5% |
7 |
|
Simran |
750,000 |
10.5% |
6 |
Add these columns:
|
Customer |
Loan Amount |
Rate |
Years |
Monthly EMI |
Total Payment |
Total Interest |
Monthly EMI
If:
- Loan Amount = B2
- Annual Rate = C2
- Years = D2
use:
=PMT(C2/12,D2*12,-B2)
Total Payment
=E2*D2*12
Total Interest
=F2-B2
Copy the formulas down for all customers.
Section 2 — Investment Analysis
Create:
|
Investor |
Monthly Investment |
Annual Rate |
Years |
Future Value |
|
Amit |
5,000 |
8% |
5 |
Formula |
|
Neha |
8,000 |
9% |
10 |
Formula |
|
Rahul |
10,000 |
8.5% |
15 |
Formula |
|
Priya |
7,500 |
9% |
10 |
Formula |
Future Value Formula
=FV(C2/12,D2*12,-B2,0)
Final Challenge
Create an interactive Financial Calculator where the user can enter:
- Loan Amount
- Interest Rate
- Loan Period
- Monthly Investment
- Investment Rate
- Investment Period
and Excel automatically calculates:
- Monthly EMI
- Total Payment
- Total Interest
- Future Value
- Present Value
- Number of Payments