# Loan Calculation

• ### Question

• Please how do I program this in Excel VBA

Cell C13, Enter Loan Amount as 1500 (prompt for value entered)
Cell C14, Enter processing fee in %
Cell C15, Enter insurance in %
Cell C16, Enter Interest rate in %
Cell C17,
Cell B1, Enter Period as 36 (prompt for value entered)
Cell C1, One-month moratorium (prompt for yes or no) if no then 0
Cell D1, period as 12 (prompt for value entered)
Cell E1, Calculate Principal.
Cell B5, Enter initial loan and interest

If month of loan is < Aug 2013
Set Processing Fee = 3%
Insurance = 2.25%
Interest rate = 1.75%

Else if month of loan is Aug 2013 <= month of loan < Jun 2015
Set Processing Fee = 1.75%
Insurance = 2%
Interest rate = 1.75%

Else if month of loan is >= Jun 2015
Set Processing Fee = 3%
Insurance = 2%
Interest rate = 2%

If month of loan is >= Jun 2015 then set no one month-moratorium else set one month-moratorium to 0.

Formula is Loan Amount = Processing Fee *Loan Amount + Insurance *Loan Amount +(1+ Interest rate)^Period

Principal = (Loan Amount * Processing Fee)+(Loan Amount * Insurance Fee)+ Loan Amount + one-month moratorium

one-month moratorium = ((Loan Amount + (Loan Amount * Processing Fee)+(Loan Amount * Insurance Fee) * Int_rate ) )

monthly pmt = principal * ((Interest rate *(1 + Interest rate)^period)/(1+Interest rate)^-1

initial loan and interest = monthly pmt * period

Set period as range from 1 to 36 months (differences of 1) and Loan Amount as range from 100 to 12000 (difference of 50)
Sunday, May 15, 2016 10:06 PM

### All replies

• For all of these:

Cell C13, Enter Loan Amount as 1500 (prompt for value entered)
Cell C14, Enter processing fee in %
Cell C15, Enter insurance in %
Cell C16, Enter Interest rate in %
Cell C17,
Cell B1, Enter Period as 36 (prompt for value entered)
Cell D1, period as 12 (prompt for value entered)

use code like this for each one

Range("C13").Value = CDbl(Inputbox("Enter Loan Amount"))

For this one:

Cell C1, One-month moratorium (prompt for yes or no) if no then 0

use code like

Range("C1").Value = - (Msgbox("One-month moratorium?",vbYesNo) = vbYes) * 1

For the others, just set up cell formulas to do what you want.

Monday, May 16, 2016 5:55 PM