How to make table formula with variables dynamic, by using selected value function

[Anonymous]

Hi Community,

I have made a loan amortization schedule using a table whith the formula shown below.

But I want to change the variable PV, r, and n to be dynamic using selected value function that will be picking up data from another table instead of entering the variables manualy in the table formula.

I have tried but with no luck I do not know what am I missing 

Amortization Schedule =

var PV = 240000

var r = 0.07/12

var n = 30*12

Var p = (r* PV)/(1 - POWER((1+r),-n))

var period =

SELECTCOLUMNS(

GENERATESERIES(1,360,1),

"Period", [Value]

)

var InitialSchedule =

ADDCOLUMNS(

Period,

"Beginning Balance", PV*POWER(1+r,[Period]-1) - p*DIVIDE(POWER(1+r,[Period]-1)-1,r),

"Monthly Payment", p

)

var RemainingSchedule =

ADDCOLUMNS(

InitialSchedule,

"Interest", [Beginning Balance]*r,

"Principal", [Monthly Payment]- ([Beginning Balance]*r),

"Ending Balance", [Beginning Balance]-([Monthly Payment]-([Beginning Balance]*r))

)

return

RemainingSchedule

[amitchandak · Accepted solution]

@Anonymous , I have created something similar - check if this pbix can help

https://www.dropbox.com/s/5yyx32ozwaw1ezc/10%20Financial%20Functions.pbix?dl=0

[amitchandak · Accepted solution]

@Anonymous , I have created something similar - check if this pbix can help

https://www.dropbox.com/s/5yyx32ozwaw1ezc/10%20Financial%20Functions.pbix?dl=0

[Anonymous · Accepted solution]

Thanks @amitchandak let me go through the pbix file throughly but I think this might do the trick.