I am an Auditor and want to calculate lease rental of companies vehicle charged by bank. Does Excel have any formula to calculate future installment or rental? Does Excel have a formula to make schedule of lease? Rental include Markup and the principle. How bank makes rental and schedule of lease.
Yes dear,
There is a formula for rental and lease payment in Excel and by this formula you can easily find your payment and maintain it but dear, there is also a problem, Excel can only give a calculation to you, but it's your work to maintain it and calculate separately all figures by this formula.
In this formula there are some values which you must know, then you can put it in to this formula and then you can find out all results of your payments easily. Formula is this:
Here PV is present value, FV is future value, Pmt shows your payment, I shows the interest rate on your payment, and n shows the time period.
1. Open Microsoft Excel 2007.
2. Go to 'File' and click new.
3. Click on 'Installed Templates'.
4. Â Look for and select the 'Loan Amortization' template and click create.
5. Â Enter the value of the asset being leased in the 'Loan amount' cell.
6. Â Enter the annual interest rate in the respective cell.
7. Enter the number of years for the lease in the 'Loan period in years'.
8. Enter the number of payments per year in the respective cell followed by the start date of the loan.
9. Test for accuracy. For a piece of machinery that costs $500,000 that will be purchased with a 10-year lease agreement an interest rate of 10% per annum, and one payment per year, the total annual payment should be equal to $81,372.70.
