Now combine the functions learned in this Topic to create a practical Loan & Investment Calculator.
Part A — Loan Calculator
Input Data
|
Item |
Value |
|
Loan Amount |
500,000 |
|
Annual Interest Rate |
10% |
|
Loan Period |
5 |
|
Payments per Year |
12 |
Calculations
Monthly Rate
=B3/B5
Number of Payments
=B4*B5
Monthly EMI
=PMT(B7,B8,-B2)
Total Payment
=B9*B8
Total Interest
=B10-B2
Final Layout
|
Item |
Value |
|
Loan Amount |
500,000 |
|
Annual Interest Rate |
10% |
|
Loan Period |
5 Years |
|
Monthly Rate |
Formula |
|
Number of Payments |
Formula |
|
Monthly EMI |
Formula |
|
Total Payment |
Formula |
|
Total Interest |
Formula |
Part B — Investment Calculator
Input Data
|
Item |
Value |
|
Monthly Investment |
5,000 |
|
Annual Interest Rate |
8% |
|
Investment Period |
5 Years |
|
Initial Investment |
0 |
Future Value
=FV(8%/12,5*12,-5000,0)
Add PV Calculation
=PV(8%/12,5*12,-5000)
Practice
Create a single worksheet containing both:
Loan Calculator + Investment Calculator
The user should be able to change the input values and see the results update automatically.