You are here:

# Personal Investment & Financial Planning Q`s/Loan Payment

Question
I am looking to make a personal loan. The total amount of the loan will be due in 5 months when the term expires. The annual rate will be 10%. What formula can I use in Excel to model the loan?

Thanks,

Chris

Hi Chris,

The formula you can use is the Future Value (FV) formula.  This will tell you the interest due after five months.   Here is what it looks like (assuming monthly compounding).

RATE   0.80%   Interest Rate - for each period
NPER   5   Number of Periods - five months
PMT   0   No payments - single repayment
PV   10,000   Present Value - Assumed Amount of Loan
Type   End of Period   Interest accrues at the end of each period principal repaid at end

FV   (\$10,404.73)   The amount paid to lender at the end of the term

=FV(rate,nper,pmt,pv.type)   This is the formula that pops up if you type =FV in cell

Check   1.0999   the check formula is (1.008)^12   confirms the monthly compounding interest rate equals 10% annually.

You can easily just use a calculator:  (.1 / 12)*5*10,000 = \$416.67 interest due.  Pretty close.

Hope this is helpful.  Good luck.
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment No Comment

Personal Investment & Financial Planning Q`s

Volunteer

#### John Guilford Kerr, CFP, CFA

##### Expertise

I can answer questions related to personal financial planning, asset allocation, private equity investment analysis, fixed income, mutual fund, ETF, and stock questions, estate planning (basic), education planning, insurance planning, and business financial planning.

##### Experience

I have thirty-eight years of experience in finance, in the U.S. and internationally, for both public and private corporations. I have specialized in personal and small business financial planning and investment advisory services since 2005.------ - ten years commercial banking, New York, Paris, Houston, Charlotte, Atlanta---- - six years treasurer of a publicly traded health care company, Philadelphia----- - six years international project finance (Bechtel), San Francisco, Manila, London----- - five years head of international hotel development (Hyatt), Chicago---- - eleven years personal financial planning.----- Registered Investment Adviser, Hawaii---- FINRA General Securities Representative - Series 7---- FINRA Uniform Combined State Law - Series 66---- Resident Producer Insurance License, Hawaii-, Accident and Health or Sickness, Life, Variable Life and Variable Annuities

Organizations
CFA Institute of Hawaii---- Certified Fiancial Planner Board of Standards, Inc-----. National Association of Personal Financial Advisers----- Financial Planning Association----- National Association of Tax Professionals

Publications
Project Finance Yearbook - 1997 / 1998 "Manila Water & Sewerage System Concession"----- "The Road To Helsinki, An Analysis of European International Relations Leading to the Conference On Security and Cooperation in Europe"' published 2015.

Education/Credentials
Certified Financial Planner, Certified Financial Planner Board of Standards, Inc.---- Chartered Financial Analyst, CFA Institute----- Certificate in Financial Planning, Boston University---- Masters in Foreign Affairs - University of Virginia---- Bachelors of Arts, Government - Ohio University