Question

How to create spreadsheet in the area of loan and lease?

Answer 1

Answer:

1. Click the "File" menu and click the "New" option.
2. Click "Office.com Templates" and enter "loan amortization" in the search bar.
3. Click "Office.com Templates" and enter "loan amortization" in the search bar.Click the loan amortization template you want to use. ...
4. Click "Office.com Templates" and enter "loan amortization" in the search bar.Click the loan amortization template you want to use. ...Fill in the principal loan amount, annual percentage rate, the term of the loan in either years or months, and the start date of the loan

Answer 2

Explanation:

use the Payment function, there are three parameters you need to know, and two optional parameters you can use:

Interest rate

Number of periods

Initial amount (PV)

Amount when finished (FV)

Payment type (i.e. time)

The syntax for the Payment function (PMT) is:

=PMT(rate, periods, pv, [fv], [type])

Open the spreadsheet template from the top of this tutorial and go to the Basic workbook, or type in what's in the image below—or your own numbers that'd correspond to the same parameters. These are typical numbers you might have when buying a house:

Since we want to know the monthly payment, we have to convert all the values to months. That means dividing the yearly interest rate by 12, and multiplying the number of years by 12. To do that, click cell B10 in your spreadsheet and enter the function:

=pmt(B4/12,B5*12,B3,B6,1)

Notice that the result has a negative value: -1,427.49.

The reason it's negative is because it's a cash outflow. If that looks strange, you can fix it either by making the loan amount negative—which might look equally strange—or by editing the formula to put a negative sign before the loan amount in cell B3. To do that, edit the formula to be the following:

=pmt(B4/12,B5*12,-B3,B6,1)

That makes the Monthly payment a positive number, as you'd expect: