Question

Using Excel Create a Two Variable Data Table for APR & Cost

Please show step by step and what excel formula is used

I already have the answers in form of amount just need to figure out how to get the answers

Answer 1

1) Rate of interest=4%

Term=30 years

Loan amount= $150000-$10000=$140000

Excel formula used for calculating monthly payment: = pmt(4%/12,30*12,140000)

2) Formula to calculate APR for different rates and loan amount

Select the whole table

Go to data column in excel file- select what if analysis

A dialogue box will appear asking two inputs

row input cell= give reference to cell where your initial APR 4% is written

column input cell=give reference to cell where your initial No. of years of loan 30 is written

Press enter

you will receive your answer.