Go Back

How to Calculate an Original Loan Amount in Excel

We can calculate an original loan amount by using the Present Value Function (PV) if we know the interest rate, periodic payment, and the given loan term. This function tells the present value of an investment.The steps below will walk you through the process of calculating an original loan amount.

Figure 1: Original Loan Amount

Setting up the Data

We will input the values as shown in figure 2 into Column A and Column B

Figure 2: Data to Calculate an Original Loan Amount

Syntax

PV(rate, nper, pmt)

Explanation

  • Rate

The rate is calculated as the interest rate per period. If we collectively obtain a loan at a 15% annual interest and make monthly payments, the interest rate per month is 15%/12 or 0.0125. We can input any of the following as the rate:

  • 0.0125
  • The cell containing the interest rate divided by 12
  • 15%/12
  • Nper

This is the total number of payments we will make for the loan. Assuming we are to pay the loan monthly in four years, the period is 4*12 (48) periods. We will input this value into the formula.

  • Pmt 

This is the amount we will pay for each month within the four-year period. This amount covers only the principal which we collected and the interest.

  • Formula

=PV(B3/12,B5,B4)

We will type or copy and paste this formula into Cell B8.

Figure 3: Inserting the Formula to Calculate the Original Loan Amount

We will now press ENTER

Figure 4: Result of the Original Loan Amount

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
how do I calculate the APR on my loan if I have the following information: 1) date loan started 2) principal amount of loan advanced 3) accrued interest 4) compounded value of loan (principal plus interest) 5) The number of days outstanding? 6) how do I determine the APR being charged
An Excelchat Expert solved this problem in 27 mins!
On the Online worksheet, in cell B11, calculate the end of month payment amount for the loan given the loan amount, term, and interest for the Online Solutions company. Use cell references where applicable. The result should be a positive value. Copy the formula over to D11.
An Excelchat Expert solved this problem in 26 mins!
IF(F13<0.2*F12,(F12*0.005)/12,"Not Required") =IF(downpayment<0.2*loan amount,(loan amount*0.005)/12,"Not Required")
An Excelchat Expert solved this problem in 29 mins!
Here are some problems that our users have asked and received explanations on
What fx formula do i use to calculate the loan amount outstanding after a certain period
Solved by O. J. in 28 mins
I need to fill out G12:G14 in tab "Lender Loan Tape Summary". I need to calculate the % of: total loan amount (col. P, "Historical Loan Tapes"), in the most frequent states (G9:G11), divided by the total loan amount. This needs to be referenced by Lender in cell B4 of "Lender Loan Tape Summary" tab. % of (loans originated in these states/total originated volume by lender)
Solved by B. C. in 24 mins
Calculate the payment amount for the loan in cell C15. Reference the cells containing the appropriate loan information as the arguments for the function you use. Cells C20C67 in the "Payment" column are populated with the payment amount from cell C15. [34 points]
Solved by M. W. in 28 mins

Leave a Comment

avatar